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.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!
Elipum Clearly, the texts you included in your question contain no duplicates. So, there must be something else triggering the warning. And I suspect that you are trying to set relationships between two tables.
- If you copy those values in a blank excel, and set the conditional formating of: Highlight duplicate values, does this error also happen to you?
Elipum No. None are highlighted. But that may be because I'm copying some texts from this web site. Why don't you upload a file that demonstrates the problem?
there's your duplicate values:
If they're phase numbers to a project you should format them as text or use a hyphen to make sure excel will automatically format them as text
I also noticed that you did put an " ' " and I also formatted the cells as text and yet the INT() function was still able to convert them as integers, now if the excel engine does that then they will always be duplicate values so then I added .00 after the first decimal point:
and it produced an error. #value as it does when I added a hyphen:
so its probably safer to either add a hyphen or add a decimal with trailing zeroes in order to be consistent
Elipum I'm struggling with a similar issue with non-duplicate cells being conditional-formatted as duplicate.
In my case (by complete chance) I may have stumbled on the error.
I have found two cells with "12-14" and "14-12" which throw up a duplicate.
Both cells are formatted as "Text".
I suspect the non-numerical entry (for me the minus sign) may be the problem.
Not very helpful though ... as I don't have a solution!
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.
Replied on May 13, 2019Report abuseYou 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
