Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Comparing two lists in Excel
Hi guys,
I am hoping someone can have me come up with a formula: It might be best to lay it out so here is what my spreadsheet looks like: ID Amount ID Amount 1234 $100 4213 $80 2134 $80 1234 $100 3124 $120 2134 $80 4213 $100 4412 $60 If it helps, I am doing a reconciliation. Essentially, if the ID exists in both lists and is the same, then it's fine and nothing needs to happen (in the above example, that would be for ID 1234 and 2134). However, if an ID is only on one list or if the amounts are not equal, I want it to be flagged. I was trying to do two separate vlookups and then compare but I have been left scratching my head. Can anyone suggest any ideas on what the best approach is? Your help would be greatly appreciated. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing two lists in Excel
"Ada33" wrote:
ID Amount ID Amount 1234 $100 4213 $80 2134 $80 1234 $100 3124 $120 2134 $80 4213 $100 4412 $60 If it helps, I am doing a reconciliation. Essentially, if the ID exists in both lists and is the same, then it's fine and nothing needs to happen (in the above example, that would be for ID 1234 and 2134). However, if an ID is only on one list or if the amounts are not equal, I want it to be flagged. Suppose your data are in columns A and B on the left and columns D and E on the right, with the titles in row 1. Enter the following formulas into the indicated cells and copy down as needed. C2: =IF(A2="","", IF(AND(COUNTIF($A$2:$A$1000,A2)=1,COUNTIF($D$2:$D$ 1000,A2)=1), IF(VLOOKUP(A2,$D$2:$E$1000,2,0)=B2,"","ERROR"),"ER ROR")) F2: =IF(D2="","", IF(AND(COUNTIF($A$2:$A$1000,D2)=1,COUNTIF($D$2:$D$ 1000,D2)=1), IF(VLOOKUP(D2,$A$2:$B$1000,2,0)=E2,"","ERROR"),"ER ROR")) This assumes each ID should appear only once in each column, and the amounts should be the same. If you want to allow for multiple appearances of the same ID and only the total of each sum should be the same (partial invoices and partial payments, not necessarily a one-to-one match), try the following: C2: =IF(A2="","", IF(AND(COUNTIF($A$2:$A$1000,A2)=1,COUNTIF($D$2:$D $1000,A2)=1), IF(SUMIF($A$2:$A$1000,A2,$B$2:$B$1000)=SUMIF($D$2: $D$1000,A2,$E$2:$E$1000), "","ERROR"),"ERROR")) F2: =IF(A2="","", IF(AND(COUNTIF($A$2:$A$1000,D2)=1,COUNTIF($D$2:$D $1000,D2)=1), IF(SUMIF($A$2:$A$1000,D2,$B$2:$B$1000)=SUMIF($D$2: $D$1000,D2,$E$2:$E$1000), "","ERROR"),"ERROR")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing lists in Excel/Visual Basic | Excel Programming | |||
comparing two lists in excel | Excel Discussion (Misc queries) | |||
Comparing 2 Excel Lists and Appending if entries are in both | Excel Discussion (Misc queries) | |||
Comparing two lists in excel that don't match exactly | Excel Worksheet Functions | |||
Comparing Lists to Partial Lists | Excel Programming |