Counting Unique Observations

[Guest]

Does anyone know how to program a function in VBA that will count the total
number of unique observations in an array?

I imagine that the syntax would be:

=UNIQUE(array)

For instance, if an array of 5 observation look like this

apple
orange
apple
apple
banana

the Unique() function would return 3.

I am aware that you can do this with a pivot table, but the unique function
would be a lot more convenient. It is also probable there is another way to
do this with built-in excel functions.

Please let me know if you have any ideas.

Thanks,
Henrik

 

[Frank Kabel]

Hi
a worksheet formula
=SUMPRODUCT((array<>"")/(COUNTIF(array,array)+(array="")))

 

[Bob Phillips]

For information, here is a UDF

Function Unique(inArray As Range) As Long
Dim colItems As Collection
Dim cell As Range

Set colItems = New Collection
On Error Resume Next
For Each cell In inArray
colItems.Add cell.Value, CStr(cell.Value)
Next cell
Unique = colItems.Count

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)