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!

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"))