Weird. Duplicated values highlighted in Excel, but they are clearly not duplicates!

[Elipum]

In excel, I am getting warning of duplicated values from this list. This prevents me from creating a data model relationship one to many. However, the values are not duplicated at all:

2.05.120
2.05.180
2.05.240
2.07
2.08
2.08.120
2.09
2.10

Isn’t it weird? Have you experienced something like this previously, and if so, is there a known solution for it?

Thanks!

[#2]

@SkylerM 

That's bit different issue. Comparing texts which looks like numbers conditional formatting converts them to numbers and compares the latest. Comparing the numbers all what after first 15 digits is ignored. Same behaviour has COUNTIF().

To prevent conversion we may use such trick:

[#3]

solution at https://answers.microsoft.com/en-us/msoffice/forum/all/failed-to-highlight-duplicate-values-in-excel... @Elipum 

cut and paste solution here. 

Andreas Killer

• Volunteer Moderator 
  |

 

• Article Author

Replied on May 13, 2019Report abuse

You are right, Excel highlight that as duplicates, because within the default conditional formatting rule Excel converts text that looks like numbers as numbers.

Within Excel, the same thing happens as if you apply this formula:
B1: =VALUE(A1)
As you can see in the screenshot below, the numbers becomes the same. This is due to the max. possible precision.
https://support.microsoft.com/en-us/help/78113/...

You can avoid that if you add any non numerical char to the number (at front or end).
C1: ="#"&A1
Now Excel "sees" the text as text, does no conversation and compares the strings

[#4]

@SkylerM 

I have the same problem as you. this problem is because excel can only distinguish duplicates with content in cells no more than 15 characters. but I don't know how to extend the character limit