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

%3CLINGO-SUB%20id%3D%22lingo-sub-2822510%22%20slang%3D%22en-US%22%3EWeird.%20Duplicated%20values%20highlighted%20in%20Excel%2C%20but%20they%20are%20clearly%20not%20duplicates!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822510%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20excel%2C%20I%20am%20getting%20warning%20of%20duplicated%20values%20from%20this%20list.%20This%20prevents%20me%20from%20creating%20a%20data%20model%20relationship%20one%20to%20many.%20However%2C%20the%20values%20are%20not%20unique%20at%20all%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%2264%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.05.120%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.05.180%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.05.240%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.07%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.08%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.08.120%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.09%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CP%3E2.10%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIsn%E2%80%99t%20it%20weird%3F%20Have%20you%20experienced%20something%20like%20this%20previously%2C%20and%20if%20so%2C%20is%20there%20a%20known%20solution%20for%20it%3F%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2822510%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822546%22%20slang%3D%22en-US%22%3ERe%3A%20Weird.%20Duplicated%20values%20highlighted%20in%20Excel%2C%20but%20they%20are%20clearly%20not%20duplicates!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822546%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600734%22%20target%3D%22_blank%22%3E%40Elipum%3C%2FA%3E%26nbsp%3BClearly%2C%20the%20texts%20you%20included%20in%20your%20question%20contain%20no%20duplicates.%20So%2C%20there%20must%20be%20something%20else%20triggering%20the%20warning.%20And%20I%20suspect%20that%20you%20are%20trying%20to%20set%20relationships%20between%20two%20tables.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822555%22%20slang%3D%22en-US%22%3ERe%3A%20Weird.%20Duplicated%20values%20highlighted%20in%20Excel%2C%20but%20they%20are%20clearly%20not%20duplicates!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822555%22%20slang%3D%22en-US%22%3EIf%20you%20copy%20those%20values%20in%20a%20blank%20excel%2C%20and%20set%20the%20conditional%20formating%20of%3A%20Highlight%20duplicate%20values%2C%20does%20this%20error%20also%20happen%20to%20you%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822585%22%20slang%3D%22en-US%22%3ERe%3A%20Weird.%20Duplicated%20values%20highlighted%20in%20Excel%2C%20but%20they%20are%20clearly%20not%20duplicates!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600734%22%20target%3D%22_blank%22%3E%40Elipum%3C%2FA%3E%26nbsp%3BNo.%20None%20are%20highlighted.%20But%20that%20may%20be%20because%20I'm%20copying%20some%20texts%20from%20this%20web%20site.%20Why%20don't%20you%20upload%20a%20file%20that%20demonstrates%20the%20problem%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822657%22%20slang%3D%22en-US%22%3ERe%3A%20Weird.%20Duplicated%20values%20highlighted%20in%20Excel%2C%20but%20they%20are%20clearly%20not%20duplicates!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822657%22%20slang%3D%22en-US%22%3EOf%20course.%20Just%20loaded%20the%20file%20to%20the%20main%20post%20now.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822713%22%20slang%3D%22en-US%22%3ERe%3A%20Weird.%20Duplicated%20values%20highlighted%20in%20Excel%2C%20but%20they%20are%20clearly%20not%20duplicates!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600734%22%20target%3D%22_blank%22%3E%40Elipum%3C%2FA%3E%26nbsp%3B%3F%3F%3F%3F%20There%20are%20no%20duplicates%20in%20this%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2822730%22%20slang%3D%22en-US%22%3ERe%3A%20Weird.%20Duplicated%20values%20highlighted%20in%20Excel%2C%20but%20they%20are%20clearly%20not%20duplicates!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2822730%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Breally%3F%20See%20screenshot.%20They%20do%20show%20as%20duplicates%20on%20my%20file%20(all%20red).%3C%2FP%3E%3CP%3EIf%20you%20delete%20any%20of%20the%20cell%20values%2C%20one%20of%20the%20other%20cells%20becomes%20white%2C%20meaning%2C%20its%20duplicate%20has%20been%20deleted.%20But%20obviously%20they%20were%20not%20duplicates%20at%20all!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

14 Replies

@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?

Of course. Just loaded the file to the main post now.

@Elipum ???? There are no duplicates in this file. 

@Riny_van_Eekelen really? See screenshot. They do show as duplicates on my file (all red).

If you delete any of the cell values, one of the other cells becomes white, meaning, its duplicate has been deleted. But obviously they were not duplicates at all!

May this be an Excel bug then? it has no sense...

@Elipum 

Could you share the file with conditional formatting rule applied?

@Sergei Baklan , the file I uploaded already has the conditional formatting applied.

Elipum_1-1634126201618.png

 

 

@Elipum 

Thank you, but my Excel shows no duplicates in your file

image.png

I don't know what's the reason, sorry. By the way, some cells in above range have General format, some hh:mm. Is that intentionally?

Thanks for the time Sergei. The format I gave them was Text for all. I don't know why it is changing when opening in your side. Maybe that is part of the use. Some cache or big on my side..?
They are meant to be formated as text all. Weird

I'm sure there is a simple explanation for everything.  You just have to abandon your "weird" thoughts, and look for the obvious.  For example....

 

-----

@Elipum  wrote: ``The format I gave them was Text for all. I don't know why it is changing when opening in your side.``

 

The __type__ of the data is text.  That is, ISTEXT returns TRUE for all of A1:A8.  But that is because the data is prefixed with an apostrophe (single-quote).  You would not have had to do that if the cell formats were Text.

 

In contrast, the __format__ of the cells is a mix of General (A1:A3, A6) and Custom "h:mm" (A4:A5, A7:A8).  Look for yourself to confirm.  But the cell format does not matter.  We can have text __values__ in cells with numeric formats, and numeric __values__ in cells that are formatted as Text.

 

-----

@Elipum  wrote: ``Duplicated values highlighted in Excel, but they are clearly not duplicates!``

 

Just because they have the same appearance as the Condition Formatting, that does not mean that CF caused the appearance.

 

To confirm, temporarily change the CF font and fill colors.  I suspect that you will not see any changes in A1:A8.

 

OTOH, I note that the __style__ of A1:A8 is a __custom__ style called Normal 2.  In contrast, by the default, the cell style is Normal.  (Errata.... The style for A1 is Percent!  But I suspect the following applies to it as well!)

 

On my system, the characteristics of Normal 2 is the same as Normal.  That is why __we__ do not see any difference in font and fill color.

 

But I suspect __your__ Normal 2 style specifies the same font and fill color as your CF.  That is why it __appears__ as if the CF applied.  (It did not.)

 

Right-click Normal 2, and click Modify.  This is what I see.

 

normal2.jpg

 

But I suspect you see the following, which is the Bad style for me:

 

bad p1.jpg

 

Drill down on Format > Font and Format > Fill to see the details.

 

I can only guess why __we__ do not see the same characteristics for the Normal 2 style.  Perhaps those details are in the local computer's Registry, not in the Excel file.  (Surprise!)

 

And then there is the anomaly of style Percent in A1 to explain.  "The exercise is left for the student".  (wink)  My wild guess:  A1 is also the Normal 2 style for you.