Forum Discussion
Weird. Duplicated values highlighted in Excel, but they are clearly not duplicates!
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.1...
solution at https://answers.microsoft.com/en-us/msoffice/forum/all/failed-to-highlight-duplicate-values-in-excel/64ae9a00-71e9-4db2-afb7-e6bb8c978a80 Elipum
cut and paste solution here.
https://answers.microsoft.com/en-us/profile/d7699ebf-3feb-4969-8f5e-510c3f71d696
Replied on May 13, 2019Report abuse- http://answers.microsoft.com/en-us/page/faq#faqWhosWho1|
- http://answers.microsoft.com/en-us/page/faq#faqWhosWho
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/floating-point-arithmetic-may-give-inaccurate-results-in-excel
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
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/floating-point-arithmetic-may-give-inaccurate-results-in-excel
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