Higlight duplicates positive and negative numbers

**magman1984** · 02-08-2011, 04:06 AM

Hi!

I have one debit and one credit column. I want to higlight duplicates, with positive and negative values.

Like this.

Column A
5000
1000
10000
3000

Column B
- 10000
-5000
-3000
-2000

I know how to do the conditional formating to find duplicates when there is only positive or negative numbers, but i don't know how to match both positive and negative numbers.

**DonkeyOte** · 02-08-2011, 05:10 AM

Highlight your values selecting from A1 onwards and apply the following conditional format rule:

Please Login or Register  to view this content.

**magman1984** · 02-08-2011, 06:13 AM

Thank you very much, it's just what I wanted!

But what if everything I want to match is in column A?

I tried the following code, but that didn't work.

Please Login or Register  to view this content.

**DonkeyOte** · 02-08-2011, 06:38 AM

Not entirely sure I follow but perhaps:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

the above assume you want to highlight 10 if -10 appears elsewhere and vice versa

**magman1984** · 02-08-2011, 07:28 AM

I should have thought about that solution myself.

Thank you! I appreciate the help!

**magman1984** · 02-08-2011, 07:42 AM

Originally Posted by DonkeyOte

Highlight your values selecting from A1 onwards and apply the following conditional format rule:

Please Login or Register  to view this content.

One more thing. In my first question with two columns, A and B, it worked great with the formula above. But let us say I want to use column F and G. Why does only the values in column G get painted red when I use the formula below?

Please Login or Register  to view this content.

**DonkeyOte** · 02-08-2011, 07:45 AM

You would use:

Please Login or Register  to view this content.

rather than =1 (ie the column number of first column being compared)

for a more transparent version you could use

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**magman1984** · 02-08-2011, 08:00 AM

Thanks again!

Thanks for the explanation of what the code below ment. I'm trying to learn how to see the logical meaning of formulas, and this sort of things helps.

Please Login or Register  to view this content.

**jrlee31** · 05-23-2011, 04:22 AM

Hi Guys,

the formula =MATCH(-A1;A:A;0) which i've change to =MATCH(-B1;B:B;0)
did NOT work for me.

when i go to Conditional Formating > New Rule > Use a forumla >

i copy and paste the =MATCH(-B1;B:B;0) on the Edit the rule Description box and it gives me an error message

What I did for the meantime is to multiply negative values to -1 and then apply the simple duplicate values.
But i know theres a faster way to do it.

How can i do this in my example table below? it's all in the same column B

Ref # Total
123 50
456 67
abc -50
efg -67
789 34
xyz 56

**McYid** · 02-13-2014, 01:07 PM

Hello,

Was hoping this would help me, but none of the above formula are working...

I have several columns of data with duplicate numbers (both positive and negative) and I want to be able to highlight one anywhere there is a dulpicate.

As an example in the attached spreadsheet I'de like cell G11 and L10 to be highlighted as they are the same value (plus any other cell that matches that figure) - but that isn't the only amount I need to highlight - F15 and L14 should also ping.

Basically I need to have a formula that will search all the figures in columns f:l and highlight any there are multiples of regardless of if they are positive or not.

As a forewarning - I'm awful at Macros, so would like to avoid if I can.

Thanks

Source 1 Source 2 Source 3 Source 4 Source 5 Source 6 Source 7
1,466.94
4,500.00
108,487.50
9,100.00
2,500.00
27.95
27.95 -27.95
15,000.00
-15,000.00
15,000.00
31,386.25
8,930.00
-8,930.00
8,930.00