VBA code for matching two excel files and multiple sheets

S

spearhead

New member
Joined
Nov 30, 2015
Messages
11
Reaction score
0
Points
0
Guys,

I am in need of big help. My boss has told me to prepare a comprehensive report to be sent to the management every month. I have searched everywhere for two days without much luck on doing the following to complete my report. Following are the issues I am trying to find a solution for. Since I am a newbie when it comes to VBA codes, so please can you help me with the following. I have some VBA codes already, so therefore please provide me the codes for the following to go with the ones i already have.

My requirement is to,

1) A VBA code to match the values in column "D" in sheet "BDS Download List" with the values in column "J-M" in sheet "Received Mandates". If a match is found in either of column J, K, L, M, a remark should be updated in column "E" as "Received" in sheet "BDS Download List".
Columns J, K, L and M will sometimes have the same value in several places, therefore the matching should be considering the latest date. The match result should be displayed in corresponding cell (same row). This is in test file 2.xlsm
Eg: if no. 12345 is available in column "J" on 20.11.2015 and again the same no. in column "L" on 28.11.2015, the VBA code should only match the 28.11.2015 record.


2) A VBA code to update value "RTN" in column "D" in sheet "Received Mandates" (test file 2.xlsm) if the no. entered in columns "F" or "G" (in "Return Mandates" sheet in excel file test file 1.xlsm) matches the no. available in either Columns J, K, L or M (in "Received Mandates" sheet excel file test file 2.xlsm). The match result should be displayed in corresponding cell (same row). Same as above the no. should be the latest entered no.
Eg: if test file 1.xlsm ("Return Mandates" sheet column "F" or "G") file has no. 12345 entered on 28.11.2015, and test file 2.xlsm ("Received Mandates" sheet column "J", "K", "L" or "M" has 12345 on 20.11.2015 and 27.11.2015, the "RTN" value should be updated on 27.11.2015 record row.

3) The code requested in point 1 should check whether there is a "RTN" value in column "D" in sheet "Received Mandates" (test file 2.xlsm) if the value is available, the value returned in point 1 should be "Not Received" if no "RTN" value the value returned should be "Received"

4) If any of the manually entered figures are deleted by the user which is required for the VBA code processing, the value automatically updated from the VBA code should also be deleted by the Code.

5) I am getting a runtime error '424' object not found in column "I" sheet "Received Mandates" (test file 2.xlsm). Please tell me what to do for this too.

Hope you can help me on this. Sorry for all the trouble. And thank everyone of you in advance. Attached the sample files for your reference.
 

Attachments

  • test file 1.xlsm
    17.9 KB · Views: 18
  • test file 2.xlsm
    62.6 KB · Views: 13
Are you familiar with MS Queries (SQL in Excel - OLEDB)? You can create a simple SELECT query that will compare multiple worksheets/columns etc. in various workbooks etc. You can create these queries by going to Data->From Other sources->From MS Query.

I also wrote an AddIn that makes it easier:
analystcave.com/excel-tools/excel-sql-add-in-free/
The AddIn also has a compare worksheet feature that create such queries within the current workbook
 
AnalystCave said:
Are you familiar with MS Queries (SQL in Excel - OLEDB)? You can create a simple SELECT query that will compare multiple worksheets/columns etc. in various workbooks etc. You can create these queries by going to Data->From Other sources->From MS Query.

I also wrote an AddIn that makes it easier:
analystcave.com/excel-tools/excel-sql-add-in-free/
The AddIn also has a compare worksheet feature that create such queries within the current workbook
Click to expand...

No bro. Sorry. I dont know MS queries.

How do I get the add-in. I dont think our IT policy will allow to install add-ins.
 
You don't need to install anything - just put it in
C:\Users\[your username]\AppData\Roaming\Microsoft\AddIns
and enable on Developer Tab in Excel.
 
AnalystCave said:
You don't need to install anything - just put it in
C:\Users\[your username]\AppData\Roaming\Microsoft\AddIns
and enable on Developer Tab in Excel.
Click to expand...

whats the addin name that i need to download. will that help with all the items i mentioned above.
 
A quick example below:

Say you have $Sheet1 and $Sheet2 and you want to check all common items in both sheets according to values in column A (for which the header is 'A'):

SELECT S1.* FROM [Sheet1$] as S1 INNER JOIN [Sheet2$] as S2 ON S1.A = S2.A

That is it. Similarly you can write queries needed in your description above. The Add mentioned above can be found here: http://analystcave.com/excel-tools/excel-sql-add-in-free/ but it is not required to created MS Queries in Excel although makes it much easier.
 
AnalystCave said:
A quick example below:

Say you have $Sheet1 and $Sheet2 and you want to check all common items in both sheets according to values in column A (for which the header is 'A'):

SELECT S1.* FROM [Sheet1$] as S1 INNER JOIN [Sheet2$] as S2 ON S1.A = S2.A

That is it. Similarly you can write queries needed in your description above. The Add mentioned above can be found here: http://analystcave.com/excel-tools/excel-sql-add-in-free/ but it is not required to created MS Queries in Excel although makes it much easier.
Click to expand...

Im still confused.

Can you tell me how to put a VBA codes for my above requirements.
 
You must log in or register to reply here.
Back
Top