# Uniques only?

#### Jaymond Flurrie

##### Well-known Member
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.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### MrExcel MVP
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

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
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:
Code:
``````=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.

Replies
4
Views
335
Replies
2
Views
299
Replies
3
Views
1K
Replies
17
Views
1K
Replies
1
Views
472

1,195,582
Messages
6,010,578
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back