Data comparison

H

HE

I have two sets of columns with two columns in each set. total of 4
columns.

Set A Set B

Description Amt. Description Amt.
41522 $54.99 41522
$54.99
48745 $22.99 48745
$22.99
48784 $58.45 48772
$98.01
49872 $64.25 48784
$58.45
49999 $10.25 49872
$64.25


Some of the descriptions in Set A are not found in Set B and vice
versa. I want to set this up so that I can compar the two lists by
creating a formula that will give me the difference of the two amounts.
For instance for 41522 $54.99-$54.99=0. This will tell me if
anything is off. I have been unable to do so since the rows are not in
sync. What can I do to make the descriptions from both sets line up in
the same rows. This is a very large file and it's impossible for me to
go line by line throught the entire document. Can I do this in a pivot
table?
 
A

Amy Stein

You can try creating another column with a vlookup that returns the
amount based on the description in the first set. Then create a column
next to that that subtracts the vlookup result from the 2nd set amount.
 
P

Pete_UK

Assuming your 4 columns of data are in A, B, C and D, and that you have
1000 rows of data beginning on row 2. In another column (eg E) you can
enter this formula into E2:

=B2-VLOOKUP(A2,C$2:D$1001,2,0)

and copy this down column E. Put the heading "Difference" in E1. You
can set up Autofilter on column E and on the pull-down you can select
Custom | Not Equal to and 0 (zero) to see where there is a difference.

Hope this helps.

Pete
 
Top