Counting Unique Observations
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)
"Frank Kabel" wrote in message
...
Hi
a worksheet formula
=SUMPRODUCT((array<"")/(COUNTIF(array,array)+(array="")))
--
Regards
Frank Kabel
Frankfurt, Germany
"Henrik" schrieb im Newsbeitrag
...
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
|