Solved

eXCEL COMPARISON SIMPLIFIED

Posted on 2012-03-19
2
250 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

759 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

22 Experts available now in Live!

Get 1:1 Help Now