Matching plus and minus amount from list.

[Salamullah]

Hi Excel Experts !

I have a list of 2000 rows contained positive and negative figures in column C Amount, column A & B having Document type and document number, I want to reconcile same figures of plus with minus and want to see the unique remaining number, what should I do.

Amount

251848
158515
666511
-251848
-125125
266266
-654555
-158515

 

[Canapone]

Hi,

a first answer,

in A1:A1000 numbers

in B2

=IF(COUNTIF($A$2:$A$1000,-B2),"",B2)

to be copied down in order to get numbers don't match

Better solutions will follow for sure


Cheers

 

[Salamullah]

Hi Canapone,

Many thanks for quick solution, its working but some figures are duplicate and its also showing blank like as follows :

-125152
125152
125152
665251
-665251
-665251

If extra between 3 figures can be shown as don't match that will be perfect, please help

 

[Canapone]

Hi,

I suspected it was a very "first aid" and poor solution. Let me think, while we are waiting for other solutions.

 

[Canapone]

Hi, shamelessly, in column B numbers that have not found their soul-mate =IFERROR(IF(COUNTIF(A2:$A$100,A2)<=ABS(ABS(SUMPRODUCT((ABS($A$2:$A$1000)=ABS(A2))*$A$2:$A$1000)/A2)*SIGN(SUMPRODUCT((ABS($A$2:$A$1000)=ABS(A2))*$A$2:$A$1000))),A2*(SIGN(A2)=SIGN(ABS(SUMPRODUCT((ABS($A$2:$A$1000)=ABS(A2))*$A$2:$A$1000)/A2)*SIGN(SUMPRODUCT((ABS($A$2:$A$1000)=ABS(A2))*$A$2:$A$1000)))),0),0)

<tbody>
</tbody>

Just for fun: maybe too many SIGN's, ABS's and parentheses.