PDA

View Full Version : VBA to extract data from 2 excels



kumar2008
10-23-2008, 05:39 AM
Hello,

I have 2 excels and I want to extract data using VBA based on match criteria on 2 columns. That is:

Excel1 has these 4 columns JoinDate, FirstName, LastName, Address.

Excel2 has these 9 columns: Transaction Type, Transaction Date, Transaction Number, FirstName, LastName, ProductCode, ProductName, Product Date, Payment Amount.

I would like to:

1) Read Excel1 sequentially
2) For every row having FirstName, LastName, find matches for the same FirstName, LastName in Excel2
3) Please note that Excel2 can have multiple rows matching for FirstName and LastName. I want to extract all the relevant rows matching with FirstName and LastName
4) Once the match is found, I want to extract these data: ProductCode, ProductName & TransactionDate and output to a 3rd excel, Excel3.
5) Excel3 format is: FirstName, LastName, ProductCode, ProductName, TransactionDate.

Need help to code this using VBA? Appreciate anyone sharing sample VBA source on this

Thanks

mdmackillop
10-23-2008, 05:54 AM
Can you post sample data and any coding you have tried?

kumar2008
10-23-2008, 07:34 AM
I am completely new to VBA world and the only thing I know in excel are playing with some formulaes. I have tried advanced filter options, but it did not seem like it can do this. I then tried a sample code from couple of other online sites on how to compare 2 worksheets, but it did not help.
I have collapsed sample data from 2 excels and also included the sample output data and attaching here.

Thanks