Comparing data in columns

S

solman25

I am trying to compare the following data:

code amount code amount
176824 17463 176824 17463
92674 84116.82 92674 84116.82
176827 5000 176832 4000
176828 3200 176828 3200
176832 5000 176827 5000
176831 4000 176831 5000
176844 1000

I am trying for a formula to compare the codes and when they match to
minus the amount from the other amount. The only problem is that the
codes are on different lines and some codes are missing from some
columns. What is the best way to get my results?

The answer should be:

code amount
176824 0
92674 0
176827 0
176828 0
176832 -1000
176831 1000
176844 -1000

Please help....Many thanks
 
P

Peo Sjoblom

If you have a separate list of the codes starting in F2 and the other table
is in A2:D8

=SUMIF($C$2:$C$8,F2,$D$2:$D$8)-SUMIF($A$2:$A$8,F2,$B$2:$B$8)


copy down will give you the result you have in your example

if you use one of the code columns starting in A2

=SUMIF($C$2:$C$8,A2,$D$2:$D$8)-SUMIF($A$2:$A$8,A2,$B$2:$B$8)
 
Top