Conditional Formatting "Highlight Duplicate Values" highlighting non-duplicates

Question

I have several columns that I am comparing against each other. I will give some sample data below that has issues as well as some data sets that have no issues.

When I go to do conditional formatting and highlight duplicate values, most of the information is just fine. HOWEVER, a few random cells highlight as duplicate but really aren't duplicated anywhere! So I thought it might have to do with the length of the values and Excel only checking 15 or 16 digits of the numbers, but nope, can't be that b/c I have others that are just as long and have duplicate numbers up to the 15th and 16th digit but differ after that and those don't highlight as duplicates of each other....

What's going on??? Any suggestions on how to fix this since I'm comparing multiple columns???

These are highlighted as duplicates but clearly are not:

99954000000020098841
99954000000020098858
99954000000020099008

These are not highlighted as duplicates but are just as long and duplicate each other up to the

00007964821931730792
00007964821931734868

Now to make matters stranger, if I take the set of 3 numbers that start with 999 and pull them out of the lists they are found within and paste them to a separate sheet then run a duplicate value check on just those 3 by themselves, ONLY the bottom two are marked as duplicates!!!

are they unique numbers? if the same number appears more than once in the same column it will be classed as a duplicate and therefore highlighted. — SierraOscar
@BruceWayne, there is a conditional formatting preset that will highlight duplicate values which I assume is whats being used. — gtwebb

Unknown Unknown · Accepted Answer · 2016-05-11T17:06:04

You are correct in that it has to do with Excel's 15 significant digit precision. The left-most non-zero values are considered the most significant.

On your first sample (e.g. 99954000000020098841), Excel considers it more important to retain the 9.9954 × 10¹⁹ portion than the 98,841 portion. It is comparing

99954000000020000000 to
99954000000020000000

In your fourth and fifth examples, there are leading zeroes that are not considered significant. Excel is comparing

7964821931730790 to
7964821931734860

Addendum:

Worksheet functions like COUNTIF function are ambiguous about whether a number is a number or text-that-looks-like-a-number. However, many people have trouble with lookup function like MATCH or VLOOKUP because they do treat true numbers and text-that-looks-like-a-number differently (e.g. 1<>"1" ). You can use this to your advantage.

In the following example, a Conditional Formatting rules has been created for A2:C9 using the following formula. Please not that the Applies to: range starts in the second row. This is important.

=OR(ISNUMBER(MATCH($A2, $A$1:$A1, 0)), ISNUMBER(MATCH($A2, $A3:$A$99999, 0)))

This produces the correct results for your large digit text-that-looks-like-a-number.

Conditional Formatting "Highlight Duplicate Values" highlighting non-duplicates

1 Answers