# Count Unique values only

#### GreenyMcDuff

##### Active Member
Hi guys,

I have what seemed like a simple problem at first.

In a list of data like this:

BE0002362342
BE0932894455
DE000A0DALH4
DE000A0DALH4
DE000A0DALH4
DE000A0DALH4
DE000A0DALH4
DE000A0EUMQ9
DE000A0EUMQ9
DE000A0EUMQ9

I want to count the number if entries that only appear once.

So out of the list above, if I counted the number of unique entries I would end up with 2 (the first 2).

Hope this makes sense, if not just let me know and I will try and explain it again.

Cheers

Chris

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### GreenyMcDuff

##### Active Member
It's ok. I should have spent longer thinking about it.

For anyone else that comes up with the same problem in future here is the answer:

1. Sort the list

2. Enter this formula in the column next to it

Code:
``=IF(AND(B2<>B1,B2<>B3),"Unique","Multiple")``

Hope this helps someone

#### JackDanIce

##### Well-known Member
Alternatively:
Rich (BB code):
``=IF(COUNTIF(\$B\$1:\$B\$10,B1)=1,"Unique","Multiple")``
This eliminates the need to sort your data. Change 10 to whatever the last row number of your data list is.

#### Peter Compton

##### New Member
You could also try putting the following formula in a cell below your data:

{=SUM(IF(COUNTIF(\$A\$2:\$A\$11;\$A\$2:\$A\$11)=1;1;0))}
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o>Don't forget to press Ctrl-Shift-Enter when you enter the formula as this is an array formula.</o>
<o></o>
<o>Hope this helps</o>

#### Ganjin

##### Board Regular
if data is in A1:A10
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))=1,1))

#### riteon

##### New Member
How could I change this code, so that if I had a column full of a few hundred entries similar to this;

apples
apples
apples
pears
pears
oranges
oranges
bananas
grapefruit
apple

I would get a unique count of each, like;

apples = 3
apple = 1
pears = 2
oranges = 2
bananas = 1
grapefruit = 1

Thanks

Last edited:

#### Ganjin

##### Board Regular
If Column A contains the few hundred entries
apples
apple

pears
oranges

bananas
grapefruit
then Column C just requires a CountIF
ex. formula in C1
=Countif(A:A,B1)

if Column B needs to be filled:
1) Go To The Data Tab
2) Under Sort & Filter, Click Advanced
3) Under Action, Enable Copy to Another Location
4) For List range: Select Column A
5) For Copy to: Select B1
6) Check the Box for Unique records only

##### MrExcel MVP
Hi guys,

I have what seemed like a simple problem at first.

In a list of data like this:

BE0002362342
BE0932894455
DE000A0DALH4
DE000A0DALH4
DE000A0DALH4
DE000A0DALH4
DE000A0DALH4
DE000A0EUMQ9
DE000A0EUMQ9
DE000A0EUMQ9

I want to count the number if entries that only appear once.

So out of the list above, if I counted the number of unique entries I would end up with 2 (the first 2).

Hope this makes sense, if not just let me know and I will try and explain it again.

Cheers

Chris

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A9<>"",MATCH("~"&A2:A9,A2:A9&"",0)),ROW(A2:A9)-ROW(A2)+1),1))

#### riteon

##### New Member
If Column A contains the few hundred entries
apples
apple

pears
oranges

bananas
grapefruit
then Column C just requires a CountIF
ex. formula in C1
=Countif(A:A,B1)

if Column B needs to be filled:
1) Go To The Data Tab
2) Under Sort & Filter, Click Advanced
3) Under Action, Enable Copy to Another Location
4) For List range: Select Column A
5) For Copy to: Select B1
6) Check the Box for Unique records only

Hi Ganjin,

The second column doesn't have the unique fields. How can I auto generate all the uniques (approx 100+) in column B?

Thanks

#### Ganjin

##### Board Regular

if Column B needs to be filled:
1) Go To The Data Tab
2) Under Sort & Filter, Click Advanced
3) Under Action, Enable Copy to Another Location
4) For List range: Select Column A
5) For Copy to: Select B1
6) Check the Box for Unique records only

Replies
5
Views
646
Replies
10
Views
3K
Replies
1
Views
381
Replies
3
Views
1K
Replies
4
Views
485

1,191,388
Messages
5,986,320
Members
440,017
Latest member
vasanrajeswaran

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