Matching negative and positive reconciliation

Hi all,

I need a simple way to highlight all the numbers in a column which pair the exact negative and positive. However, the remove duplicate or the ASAP utilities highlight duplicate button just don't seem to do the trick. Reason it fail is it will highlight all duplicates.

In situation where I have two -1300 and one 1300. It will highlight all 3, where I just need them to highlight the first two cell that show -1300 and 1300 and leave the next -1300 alone.

Is it possible to do this just with conditional formatting instead of some funky macros?

Thanks,
Ray

Here's a simple formulas play to achieve it ...
Assume your source values are running in A2 down
In B2: =IF(A2="","",IF(A2<0,-A2&"_"&COUNTIF(A$2:A2,A2),A2&"_"&COUNTIF(A$2:A2,A2)))
In C2: =IF(COUNTIF(B:B,B2)=2,"x","")
Copy B2:C2 down to the last row of source data
Col C will flag all matching / knock-off items as "x"
which may include multiple matches/knock-offs for the same pair of opposite values, if any

Now, you can either just filter on col C for "x" to retrieve the whole lot of matching values (Knock-off values) at one go.
Alternatively, do a CF on the data in col A, pointing to col C
Eg, select data in A2:A100 (with A2 active), apply CF using formula: =$C2="x"
Choose the formatting desired, ok out

ps: between the 2 actions: Filtering and CF, I'd think that the filtering option usually proves to be more useful for any downstream actions
------------
Success? Celebrate it, click the little star at the bottom left of my response(s)

PERFECT!
It's truly brilliant how you make use of the "_" underscore and keep count the number of pairs that have occured.

Thanks alot, you not only save my day but my colleagues too!

hi Max,

can you help me to understand what do you mean when you say,"Copy B2:C2 down to the last row of source data" kindly help as I have the same problem.

Thanks. It worked,Helped me to reconcile.