Uniques only?

[Jaymond Flurrie]

Is there any easy function that returns true/false based on whether the passed range argument has only unique values (so no value is twice on the list)?

I have so far done this so that I use two columns and make a countifs-formula that compares the value on the other column to all values on the other column and counts the amount of similar rows and then I take max of those numbers and compare that to number one. It just seems pretty complicated. And more than anything, it works only with Excel 2007.

 

[Aladin Akyurek]

Is there any easy function that returns true/false based on whether the passed range argument has only unique values (so no value is twice on the list)?

I have so far done this so that I use two columns and make a countifs-formula that compares the value on the other column to all values on the other column and counts the amount of similar rows and then I take max of those numbers and compare that to number one. It just seems pretty complicated. And more than anything, it works only with Excel 2007.

Given A1:A5

a1, 1
a2, 2
a3, 5
a4,
a5, X

what is the desired outcome? Note that A4 is an empty cell.

 

[c_m]

Try:
=(SUMPRODUCT(COUNTIF(A1:A5,A1:A5))=COUNTA(A1:A5))

where A1:A5 is the range you want to evaluate. It will return TRUE if the values are unique and FALSE if the list contains duplicates

 

[Jaymond Flurrie]

Given A1:A5

a1, 1
a2, 2
a3, 5
a4,
a5, X

what is the desired outcome? Note that A4 is an empty cell.

It's true, since 1 is not 2 is not 5 is not "" is not X

 

[Aladin Akyurek]

It's true, since 1 is not 2 is not 5 is not "" is not X

If formula-blanks (i.e., "") and empty cells are possible...

Control+shift+enter, just enter:

=SUM(IF(FREQUENCY(IF(A1:A5<>"",MATCH("~"&A1:A5,A1:A5&"",0)),
   ROW(A1:A5)-ROW(A1)+1),1))=COUNT(A1:A5)+COUNTIF(A1:A5,"?*")

Or:

=SUMPRODUCT((A1:A5<>"")/COUNTIF(A1:A5,A1:A5&""))=COUNT(A1:A5)+COUNTIF(A1:A5,"?*")

The formula assumes a range free of non-numeric and non-text values.