Solved

eXCEL COMPARISON SIMPLIFIED

Posted on 2012-03-19
2
252 Views
Last Modified: 2012-03-19
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.
0
Comment
Question by:SMadhavi
  • 2
2 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37739889
To compare the entire record from A:E - You can use an array function like this (in column e on sheet1, say E2):

[E2]=IF(ISERROR(MATCH($A2&$B2&$C2&$D2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100&Sheet2!$C$2:$C$100&Sheet2!$D2:$D100,0)),"MISSING","")

Hit CTRL-SHIFT-ENTER to confirm array.  Then, you can copy down.

And E2 on Sheet2:

[E2]=IF(ISERROR(MATCH($A2&$B2&$C2&$D2,Sheet1!$A$2:$A$100&Sheet1!$B$2:$B$100&Sheet1!$C$2:$C$100&Sheet1!$D$2:$D$100,0)),"MISSING","")

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
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37739893
If you only want to compare column A in both sheets, then

Sheet1:
[E2]=IF(ISERROR(MATCH($A2,Sheet2!$A$2:$A$100,0)),"MISSING","")

and copy down

Sheet2:
[E2]=IF(ISERROR(MATCH(A2,Sheet1!$A$2:A$100,0)),"MISSING","")

And copy down

See attached example using only the first column for matching.

Dave
excelCompFirstColOnly-r2.xls
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now