How do you Count the number of unique values in a list ?

G

Guest

Guest
I would like to know how many unique values I have in a given list / range.

Is there a formula to return this number. The data is generally alphanumeric.

Thanks
Sean
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I assume by unique numbers, you merely want to count how many different numbers appear in the list?

If so, a Pivot Table would do it quite easily.
 
Upvote 0
you could try sorting your list and using the Data-> Subtotal function.

This groups up your data and subtotals the group.
 
Upvote 0
On 2002-03-05 04:53, Anonymous wrote:
I would like to know how many unique values I have in a given list / range.

Is there a formula to return this number. The data is generally alphanumeric.

Thanks
Sean

You can also use the following formula to obtain the desired count of uniques items:

=IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))
 
Upvote 0
In addition to the responses already supplied, here's a UDF :-

Function Uniques(Rng As Range) As Long
Dim cell As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next
For Each cell In Rng
UniqueValues.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
Uniques = UniqueValues.Count
End Function
 
Upvote 0
What if you want to know not only the count of each unique item, but the items themselves? There could be small differences between the items, which you would want accounted for.

:rolleyes:

P.S. I love this forum! I've thought about posting many times, only to find the exact solution I needed! Kudos to all! (y)
 
Upvote 0
Tazguy37 said:
What if you want to know not only the count of each unique item, but the items themselves? There could be small differences between the items, which you would want accounted for.

:rolleyes:

P.S. I love this forum! I've thought about posting many times, only to find the exact solution I needed! Kudos to all! (y)

For a list of unique items, see,

http://216.92.17.166/board2/viewtopic.php?t=8659

Aladin's revised method
 
Upvote 0
Hi Taz:

Welcome to MrExcel Board!

Do you mean to say you would like to extract all those items that are unique (meaning suppressing the duplicates)? -- if so then the UNIQUEVALUES function from the Morefunc add-in should do the job.

I am sorry ... I did not understand the part of your statement ...
There could be small differences between the items, which you would want accounted for.
 
Upvote 0
Yogi Anand said:
Hi Taz:

Welcome to MrExcel Board!

Do you mean to say you would like to extract all those items that are unique (meaning suppressing the duplicates)? -- if so then the UNIQUEVALUES function from the Morefunc add-in should do the job.

I am sorry ... I did not understand the part of your statement ...
There could be small differences between the items, which you would want accounted for.

Thanks for the reply, Yogi! Yes, UNIQUEVALUES does the job just great, however -- not everyone that sees my work likes to install add-ins on their PC (or knows how, truth be told :oops: ).

In the 2nd part of my post, I just meant that you'd want to count all the unique items, not just ones that are "close." You can ignore that part...no worries.

Thanks for all the help! (y)
 
Upvote 0
Tazguy37 said:
...
Yes, UNIQUEVALUES does the job just great, however -- not everyone that sees my work likes to install add-ins on their PC (or knows how, truth be told :oops: ).

My contrib in

http://216.92.17.166/board2/viewtopic.php?t=91929&highlight=distinct

describes a fast formula system to extract unique/distinct items from a range.

Method 1 (Brian refers to) in:

http://216.92.17.166/board2/viewtopic.php?t=8659&postdays=0&postorder=asc&start=20

sorts and extracts such a list.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top