eXCEL COMPARISON SIMPLIFIED
I have two sheets
sheet1 has col1, col2, col3, col4
and
Sheet2 has col1, col2, col3, col4
The data in both the sheets is sorted as col2, col1
I want to run a comparison on two sheets, I want find the records missing in sheet2 that are present in sheet 1
We need to comapre col1
Example of data in sheet 1
col1, col2, col3, col4
a.b d 1 1
d.c d 2 1
E.F D
w.s e
e.d e 1 1
Example of sheet2
col1, col2, col3, col4
a.b d 1 1
w.s e
FINAL SHEET1 SHOULD LOOK LIKE THIS
col1, col2, col3, col4 COL5
a.b d 1 1
d.c d 2 1 MISSING
E.F d MISSING
w.s e
e.d e 1 1 MISSING
pLEASE NOTE i AM NOW COMPARING ONLY cOL1 FROM SHEET1 TO COL1 FROM SHEET2
No idea how to do it in excel.
sheet1 has col1, col2, col3, col4
and
Sheet2 has col1, col2, col3, col4
The data in both the sheets is sorted as col2, col1
I want to run a comparison on two sheets, I want find the records missing in sheet2 that are present in sheet 1
We need to comapre col1
Example of data in sheet 1
col1, col2, col3, col4
a.b d 1 1
d.c d 2 1
E.F D
w.s e
e.d e 1 1
Example of sheet2
col1, col2, col3, col4
a.b d 1 1
w.s e
FINAL SHEET1 SHOULD LOOK LIKE THIS
col1, col2, col3, col4 COL5
a.b d 1 1
d.c d 2 1 MISSING
E.F d MISSING
w.s e
e.d e 1 1 MISSING
pLEASE NOTE i AM NOW COMPARING ONLY cOL1 FROM SHEET1 TO COL1 FROM SHEET2
No idea how to do it in excel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
[E2]=IF(ISERROR(MATCH($A2&
Hit CTRL-SHIFT-ENTER to confirm array. Then, you can copy down.
And E2 on Sheet2:
[E2]=IF(ISERROR(MATCH($A2&
Hit CTRL-SHIFT-ENTER to confirm array. Then, you can copy down.
The formula supports values from row 2 to 100. Just change the 100 to a larger number if you're working with larger datasets, or use A:A, B:B, C:C, D:D for the entire sheet (though, that makes for additional processing cost).
See attached demonstrated solution.
Dave
excelComparison-r1.xls