# Matching negative and positive reconciliation

1. ## 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

2. ## Re: Matching negative and positive reconciliation

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)

3. ## Re: Matching negative and positive reconciliation

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!

4. ## Re: Matching negative and positive reconciliation

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.

5. ## Re: Matching negative and positive reconciliation

Hi, welcome to the forum

On the assumption that that phrase was used somewhere in this thread, it means that you are to copy the contents of B2 and C2 down to where your data ends - if the last ro of data is roww 100, that is how far you copy it down

6. ## Re: Matching negative and positive reconciliation

Thanks. It worked,Helped me to reconcile.

There are currently 1 users browsing this thread. (0 members and 1 guests)