How to compare two columns in two worksheets

[Closed]
Report
Posts
3
Registration date
Monday January 11, 2010
Status
Member
Last seen
January 17, 2010
-
 daveez -
Hello,
i need a macro code that can compare two columns in two excel files as :


File1 File 2
A B A B
1 ID stock 1 ID stock
2 AB-C1 5 2 AB-C1 2
3 AB-C2 100 3 AB-C2 1
4 AB-C3 3 4 AB-C3 3
5 AB-C4 1 5 AB-C4 1
6 AB-C5 6 6 AB-C5 6
7 AB-C6 9 7 AB-C6 9
8 AB-C7 3 8 AB-C7 5
9 T-001 55 9 T-001 0
10 T-002 66 10 T-002 1
11 T-003 77 11 T-003 22
12 T-004 21 12 T-004 1
13 T-005 69 13 T-005 9
14 T-006 10 14 T-006 15
15 T-007 3
what i need to do is to compare columns A in file2 with column A in file1, if its do exist then replace the data of column B in file1 by the data in column B in file2 at the same row, example: AB-C1 in file2 do exist in file1 so put the data in cell B2 which its number (2), in cell B2 in file1 instead of number (5) and then color the cell A2 in any color lets say red, and so on.. P.S : not necessary the new results must be in file1, we can put it in new file or new sheet.
please help me as soon as u can.
and thank u.

2 replies

Create a new column for the results wherever you want. Use vlookup - =VLOOKUP(A19,E19:F31,2,FALSE) then copy down to all the appropriate cells. (A19 is the ID in the first cell in the first row of file 1. Replace E19:F31 with the range in File 2 of the ID (E19) and stock number (F19) E19 is the first ID in file 2 and F31 is the last amount of stock in file 2.
Copy this down to all the rows for file 1.

When run copy the results and then paste special - values.

Now apply a conditional format to each answer that compares the resulting stock amount to the stock amount in file 2. If it is the same, format the cell to be filled with RED.
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
3
Registration date
Monday January 11, 2010
Status
Member
Last seen
January 17, 2010
1
Hi Friends,

I am looking for a macro code to compare two excel files if the data matches it should copy the info from one file to another new file.

The comparision is like for eg: A Column (Name) and B Column( ID) in one sheet matches with the A column(EName) and B Column(Eid) then it should take the entire row from the second file and paste it another new excel file and save it. Also, it would be nice if the differences would be highlighted in a different color whether the line only occurs in the first file or in the second file.


Kindly help me out in creating this macro.

Regards,
Rukanah