Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Observations
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Unique Observations
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of observations within a date range? | Excel Worksheet Functions | |||
How to find the top 5% observations | Excel Discussion (Misc queries) | |||
Listing Unique Observations | Excel Worksheet Functions | |||
Number of Observations | Excel Discussion (Misc queries) | |||
Formulas for...1. Counting unique cells 2. Display unique contents | Excel Programming |