Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I compare two worksheets using Excel?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I compare two worksheets using Excel?
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 Cecilia wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I compare two worksheets using Excel?
This does a compare by taking the Vendor name and Invoice Key from Sheet1 and
matching against Sheet2 and compares values: =IF(INDEX(Sheet1!D2:D5,MATCH(1,(Sheet1!A3=Sheet1!A 2:A5)*(Sheet1!B3=Sheet1!B2:B5),0))=INDEX(Sheet2!D2 :D5,MATCH(1,(Sheet1!A3=Sheet2!A2:A5)*(Sheet1!B3=Sh eet2!B2:B5),0)),"OK","NOK") You could extend this to include PO number: =IF(INDEX(Sheet1!D2:D5,MATCH(1,(Sheet1!A3=Sheet1!A 2:A5)*(Sheet1!B3=Sheet1!B2:B5)*(sheet1!C3=Sheet1!C 2:C5),0))=INDEX(Sheet2!D2:D5,MATCH(1,(Sheet1!A3=Sh eet2!A2:A5)*(Sheet1!B3=Sheet2!B2:B5))*(sheet1!C3=S heet2!C2:C5),0)),"OK","Not OK") HTH "Cecilia" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I compare two worksheets using Excel?
....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. "Toppers" wrote: This does a compare by taking the Vendor name and Invoice Key from Sheet1 and matching against Sheet2 and compares values: =IF(INDEX(Sheet1!D2:D5,MATCH(1,(Sheet1!A3=Sheet1!A 2:A5)*(Sheet1!B3=Sheet1!B2:B5),0))=INDEX(Sheet2!D2 :D5,MATCH(1,(Sheet1!A3=Sheet2!A2:A5)*(Sheet1!B3=Sh eet2!B2:B5),0)),"OK","NOK") You could extend this to include PO number: =IF(INDEX(Sheet1!D2:D5,MATCH(1,(Sheet1!A3=Sheet1!A 2:A5)*(Sheet1!B3=Sheet1!B2:B5)*(sheet1!C3=Sheet1!C 2:C5),0))=INDEX(Sheet2!D2:D5,MATCH(1,(Sheet1!A3=Sh eet2!A2:A5)*(Sheet1!B3=Sheet2!B2:B5))*(sheet1!C3=S heet2!C2:C5),0)),"OK","Not OK") HTH "Cecilia" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I compare two worksheets using Excel?
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" wrote: 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 Cecilia wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I compare two worksheets using Excel?
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 Cecilia wrote: 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" wrote: 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 Cecilia wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing worksheets in Excel and highlighting changes | Excel Worksheet Functions | |||
How do I Compare and Merge Workbooks in Excel? | Excel Discussion (Misc queries) | |||
how do I auto name worksheets in Excel? | Excel Worksheet Functions | |||
How do I sort worksheets in excel? i.e. Order them by date | Excel Worksheet Functions | |||
How do I compare data from 2 worksheets to find duplicate entries | Excel Discussion (Misc queries) |