Matching a negative value with a positive value using conditional formatting.

Good afternoon everyone,

I am new to the forum. I have been browsing and found some of the information helpful.
I was looking to find an answer to the above question, but I was not successful.
I am trying to reconcile an account with over 40,000 lines in excel.
I need to match a negative amount with a positive amount, eliminate these items so I know what makes up the ending balance. Ex. -4000 matched with a +4000.
These items are listed in one column. I would like to use conditional formatting because it has the strikethrough feature.
Any help given is greatly appreciated! If this question has already been asked and solved, please guide me to the thread containing the answer. Thank you so much.

Hi and welcome to the forum

Could you post a small sample of data in a workbook and show your expected results?
It's not entirely clear what you're attempting to achieve here. Well not to me anyway...

BSB

(7,064.00)
240.00
7,064.00
115.00
(115.00)
(240.00)
115.00

BSB, thank you for responding! Given the above data in a column I want conditional formatting to highlight one negative value with one matching positive value.

So in the case of 115.00 in your sample data, are you saying you only want one of the them highlighted because there is a -115.00?

Are you looking for each matching "pair" (one negative, one positive) to be a different colour?

BSB

Yes. So if there are 2 +115 and only 1 (115) I only want one pair highlighted and the other left unhighlighted. I only want pairs highlighted basically. So at the end all the pairs should sum to zero. Hope that makes sense.

Hi
Suppose your list from A2:...
In B2 try use these formula
=IFERROR(MATCH(-A2,A3:A$10,0)+ROW(A2),MATCH(ROW(),B$1:B1,0))
and copy down

Hi Jose,

Thank you for responding. All the data is in one column.

Yes Jenna.
All data is in column A. Column B as a number which represent the line where the pair can be found. If is not number the pair can't be found.
See the file Test23.xlsx

Thank you Jose, I will try that and let you know how it works!

Hi Jenna
Instead using the previous formula post #6 try these (resolve duplicate values better)

Formula:

Please Login or Register  to view this content.

Regards

Evening Jose,

The first formula in post 6 worked a little, but when I summed all the matches the formula identified, it did not equal to zero.

Trying the second formula, but it is not identifying all the matches.. Maybe I put it in wrong for my data.. Can you attach a test file for this formula like you did the other... Thanks again for your help.

Hi Jenna
See the file Test23(1).xlsx with the formulas (post #6 and post #10)