Can I compare two worksheets using Excel?

[Cecilia]

Hello, I am trying to comparing two worksheets with heavy data volume and
digging out the duplicated entries between the two worksheets. For example,

Worksheet 1
Vendor name Invoice Key PO Number Amount
1 ABC ER006 B0045900 $10,000
2 GHI PC001 B0050001 $12,000
3 XYZ ER009 B0049299 $12,999
4 WW PC005 B0005200 $54,000

Worksheet 2
Vendor name Invoice Key PO Number Amount
1 BBC PC005 B0005000 $50,000
2 DF PC010 B0060010 $999.99
3 GHI PC001 B0050001 $12,000
4 GHI PC002 B0050001 $5,000

The finding should be that the GHI transaction in the amount of $12,000 is
on both worksheets. (#2 on Worksheet 1 matches #3 on Worksheet 2) It would be
a tidious task and consumes a lot of time if I do it mannually. Is there a
way I can do it much faster with Excel's help?

I'd appreciate your suggestions and comments.

 

[Pete_UK]

Insert a new column A in Worksheet 2 and put this formula in A2:

=B2&C2&D2

and copy this down for as many items as you have in the sheet.

Enter this formula in E2 of Worksheet1:

=IF(ISNA(VLOOKUP(A2&B2&C2,'Worksheet 2'!A$2:E$5000,1,0)),"Not
present",VLOOKUP(A2&B2&C2,'Worksheet 2'!A$2:E$5000,5,0))

and copy this down the column - I've assumed a range of 5000 items, so
adjust this to suit your data.

Where you do not have duplicates you will see the message "Not
present", but if the record is duplicated you will see the dollar
amount from Worksheet 2 in column E of Worksheet 1, so that you can
easily compare if these are the same. To make it easier still, you
could set up a simple subtraction in column F and then scan the column
(or filter) for non-zero values.

Hope this helps.

Pete

 

[Toppers]

This does a compare by taking the Vendor name and Invoice Key from Sheet1 and
matching against Sheet2 and compares values:


=IF(INDEX(Sheet1!D25,MATCH(1,(Sheet1!A3=Sheet1!A2:A5)*(Sheet1!B3=Sheet1!B2:B5),0))=INDEX(Sheet2!D25,MATCH(1,(Sheet1!A3=Sheet2!A2:A5)*(Sheet1!B3=Sheet2!B2:B5),0)),"OK","NOK")

You could extend this to include PO number:

=IF(INDEX(Sheet1!D25,MATCH(1,(Sheet1!A3=Sheet1!A2:A5)*(Sheet1!B3=Sheet1!B2:B5)*(sheet1!C3=Sheet1!C2:C5),0))=INDEX(Sheet2!D25,MATCH(1,(Sheet1!A3=Sheet2!A2:A5)*(Sheet1!B3=Sheet2!B2:B5))*(sheet1!C3=Sheet2!C2:C5),0)),"OK","Not OK")

HTH

 

[Toppers]

....enter with Ctrl+Shift+Enter (array formula) and copy down.


There is no error checking so you will get #NA if no match so you might
prefer Pete's approach.

 

[Cecilia]

Thanks Pete...I am working on your method now...but because my actual
worksheets have a lot more columns and rows than the example that I gave,
just wondering if I should still use "1" and "5" as in your formular. I
assume they are col_index_num.
=IF(ISNA(VLOOKUP(A2&B2&C2,'Worksheet 2'!A$2:E$5000,1,0)),"Not

present",VLOOKUP(A2&B2&C2,'Worksheet 2'!A$2:E$5000,5,0))

Why is it a "1" in the first VLOOKUP and a "5" in the second VLOOKUP?

Thank you again.

 

[Pete_UK]

Yes, the 5 represents the 5th column (E) which is what you had in your
example - if you have 13 columns you would change the 5 to 13 and the E
to M (assuming starting in column A). If you have more than 5000 rows,
then change this to suit your data.

I used a 1 for the first Vlookup as there is no need to fetch the data
from the 5th column if it is not present in the first, but this could
also be set to 5 if you wish.

Hope this helps - let us know how you get on.

Pete