Compare two sheets

[pawasthi]

Hi All,

First of all, thanks to all contributors of this forum. Friends, this time I have to compare two excel sheets for data.

1. Data may be modified in cells.
2. Rows can be deleted
3. Rows can be added

So basically I have some data in old sheet but in new data sheet, some rows are added, deleted and modified. (Also the data may be sorted in the new data sheet so row sequencing could be different in both sheets)

Please help me out with this..

I have read somewhere this can be done via VLookUP..But i dnt know how..


Please find the attached sample sheets.

[shrivallabha]

Do you want to compare a specific column or all columns?

Suppose you want to check Column A (Name) "New Data" worksheet then in cell E2 paste following formula and copy it down:

=VLOOKUP(A2,'Old Data'!$A$2:$A$5,1,0)

[pawasthi]

Thanks Shri!!!!

But i have to compare the rows (All columns all together)...

[shrivallabha]

Even then it should be possible.
Step 1] On Sheet "New Data" in cell E2, insert following formula

=CONCATENATE(A2,B2,C2,D2)

Copy down the formula to the bottom-most cell. Repeat the same for sheet "Old Data".
Step 2] Now on Sheet "New Data" in cell F2, insert following formula:

=VLOOKUP(E2,'Old Data'!$E$2:$E$5,1,0)

Copy down to the bottom most row.