Comparing two lists in Excel

A

Ada33

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 look
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 exist
in both lists and is the same, then it's fine and nothing needs t
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,
want it to be flagged.

I was trying to do two separate vlookups and then compare but I hav
been left scratching my head. Can anyone suggest any ideas on what th
best approach is?

Your help would be greatly appreciated.

Thanks
 
J

joeu2004

Ada33 said:
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"),"ERROR"))

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top