# Matching plus and minus amount from list.

#### Salamullah

##### Board Regular
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Hi,

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

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

Hi,

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

 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.

Last edited:

Replies
7
Views
157
Replies
8
Views
329
Replies
4
Views
271
Replies
1
Views
147
Replies
25
Views
1K

1,203,502
Messages
6,055,772
Members
444,822
Latest member
Hombre

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back