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

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

[Nobby]

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.

[mrowe]

you could try sorting your list and using the Data-> Subtotal function.

This groups up your data and subtotals the group.

[Aladin Akyurek]

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

[Autolycus]

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

[Tazguy37]

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.



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

[Brian from Maui]

Originally Posted by Tazguy37

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.



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

For a list of unique items, see,

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

Aladin's revised method

[Yogi Anand]

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.

[Tazguy37]

Originally Posted by Yogi Anand

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 ).

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!

[Aladin Akyurek]

Originally Posted by Tazguy37

...
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 ).

My contrib in

http://216.92.17.166/board2/viewtopi...light=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/viewtopi...r=asc&start=20

sorts and extracts such a list.