Compare Data

[Shane]

Hi,

I have two workbooks. Test1 and Test2

Need a macro that will compare the values of Test1!ColumnA with Test2!ColumnA. If any occurrence of Test1!ColumnA found in Test2ColumnA then the value From Test2!ColumnB would picked up and pasted in front of matching values in Test1!ColumnD.

Attached please find a sample data. Thank in Advance for any help if i can get on this.

[grichey]

Macro probably not needed if this is all you're doing. InTest1.xls
D1: =IF(ISNA(VLOOKUP(A2,[Test1.xls]Sheet1!$A$2:$B$51,2,FALSE)),"",VLOOKUP(A2,[Test1.xls]Sheet1!$A$2:$B$51,2,FALSE))
Fill down

note: This is assuming you have both workbooks open...

[Shane]

Hi Grichey,

Thanks for your help..

I am afraid in informing you that the solution provided by you is not working. I would also like to tell you that the number of records is not fixed so i cannot use Sheet1!$A$2:$B$51 kind of a thing. I need a macro that will find the number of records and then work accordingly.

[mdmackillop]

Hi Shane,
As I understand it you are trying to learn VBA. It would be better for you to post your code for comment, rather than simply ask for a solution. There are often many ways to accomplish tasks, and a solution may have no similarity to your own attempts.
Regards
MD

[shahcu]

Hi MD,

Point taken sir. Thank you so much for the suggestion. From now onwards i will surely do that.

Regards,
Shane

[mdmackillop]

I'm getting confused as to who is responding on this IP address. If Shane asks the question, then Shane should respond. I understand the shared access, but it is confusing.

[grichey]

I'm getting confused as to who is responding on this IP address. If Shane asks the question, then Shane should respond. I understand the shared access, but it is confusing.

agreed.

Does the formula I posted aside from not being dynamic accomplish what you're shooting for?

[Aussiebear]

I'm getting confused as to who is responding on this IP address. If Shane asks the question, then Shane should respond. I understand the shared access, but it is confusing.

Shane has unfortunately a number of user names here. If he wants to continue to participate in this forum, then he needs to indicate which user name he wants to use, and the rest will be scrapped. If he doesn't indicate, in the near future, which one he wants to keep, then I'll be recommending that all user names be removed.