=unique formula giving duplicates

[Bob_ipc]

Hey all!!

I have a unique problem... pardon the pun haha.

I have a really simple equation =IFERROR(UNIQUE('Lims Report'!D7:D5000,FALSE,FALSE),"")
This formula has always worked for a simple list with no duplicates, and now I have a list using this formula that has given me a duplicate
Has anyone ever run into this before???

Thanks

 

[Eric W]

Are you sure that they are real duplicates? Is there a hidden space in one of them?

 

[Peter_SSs]

One way to check Eric's idea would be to use a formula like this in an adjacent column. In my example, you can see that the two values that appear to both be "abc" are different length (the second one has a space at the end)

20 12 29.xlsm
	A	B	C	D
1	abc		abc	3
2	def		def	3
3	abc		abc	4
4	ghi		ghi	3
5	def
Check Unique

Cell Formulas
Range	Formula
C1:C4	C1	=UNIQUE(A1:A5,FALSE,FALSE)
D1:D4	D1	=LEN(C1#)
Dynamic array formulas.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Bob_ipc]

Hey guys thanks for the speedy reply, The data set comes from an automated report, I just looked over the data and it all looks to be the same on the report but it shows up twice on the summary I am trying to build.

And when double checking using your suggestion Peter, the number with the duplicate values both had the same number of text.

 

[Peter_SSs]

the number with the duplicate values both had the same number of text.

OK, but is that number the number of characters that you can visually detect in the two cells?

It sounds like you might have some characters that have zero physical length embedded into some of the text.
Here it appears that I have 3 unique values in col R but the UNIQUE function is reporting 5. In column S both "For" and "example" are reporting the same lengths for both of those cells but the length is actually one more than the actual letters in those words. On the other hand "sample" is showing the correct number of letters in the word. BTW, none of the words in col R have spaces before or after.
Could this be the case with your data?

20 12 29.xlsm
	R	S	T
1	For	For	4
2	For	For	4
3	example	example	8
4	sample	sample	6
5	example	example	8
6	sample
Check Unique

Cell Formulas
Range	Formula
S1:S5	S1	=UNIQUE(R1:R6,0,0)
T1:T5	T1	=LEN(S1#)
Dynamic array formulas.

 

[Bob_ipc]

Hey guys! I found the problem!! so turns out the list of values had a few entries that had been formatted as text, after changing the format to them all being the same then everything worked as it should!!

Thanks guys

 

[Peter_SSs]

Glad you figured it out. Thanks for letting us know.