Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique in non-continuous list
Hi,
Is there a way to count unique values in a list that is not continuous and contains merged cells? I have a list in one column which regroups data (numbers) in several categories and each value can occur in several categories. And there are merged cells as well. How to count the unique entries over the entire list? The functions mentioned in the numerous posts here do not seem to work. Thanks, Hans |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique in non-continuous list
Hans,
Copy the code below, paste it into a regular codemodule, then use it like =UCOUNT((E4:E9,G6:G10,I6:I10)) Note the interior parens around the comma-delimited cell addresses. HTH, Bernie MS Excel MVP Function UCount(R As Variant) As Integer Dim C As Range Dim A As Range Dim V() As Variant Dim i As Integer UCount = 0 ReDim V(1 To 1) For Each A In R.Areas For Each C In A.Cells For i = LBound(V) To UBound(V) If V(i) = C.Value Then GoTo Already Next i ReDim Preserve V(1 To UBound(V) + 1) V(UBound(V)) = C.Value UCount = UCount + 1 Already: Next C Next A End Function "Hans" wrote in message ... Hi, Is there a way to count unique values in a list that is not continuous and contains merged cells? I have a list in one column which regroups data (numbers) in several categories and each value can occur in several categories. And there are merged cells as well. How to count the unique entries over the entire list? The functions mentioned in the numerous posts here do not seem to work. Thanks, Hans |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique in non-continuous list
"Bernie Deitrick" <deitbe @ consumer dot org wrote...
Hans, Copy the code below, paste it into a regular codemodule, then use it like =UCOUNT((E4:E9,G6:G10,I6:I10)) .... As Charles Williams has pointed out in the past, there can be problems passing multiple area ranges to udfs. http://www.decisionmodels.com/calcsecretsj.htm Besides, this doesn't require udfs if the values in the range are all numeric. Using your example range, try =COUNT(1/FREQUENCY((E4:E9,G6:G10,I6:I10),(E4:E9,G6:G10,I6:I 10))) For mixed text and numbers, more robust to use a udf that accepts a variable number of arguments, though that would fail when one has more than 30 ranges. Most robust would be to use a udf that returns the distinct items in the entries in its argument ranges or arrays or the arguments themselves when they're scalars, then call it nested repeatedly and pass the result through COUNTA. That is, a udf like Function u(ParamArray a() As Variant) As Variant Dim d As Object, x As Variant, y As Variant, z As Variant Set d = CreateObject("Scripting.Dictionary") For Each x In a If TypeOf x Is Range Or IsArray(x) Then For Each y In x If TypeOf y Is Range Then z = y.Value Else z = y If Not d.Exists(z) Then d.Add Key:=z, Item:=1 Next y ElseIf Not d.Exists(x) Then d.Add Key:=x, Item:=1 End If Next x u = d.Keys End Function and call it like so =COUNTA(u(E4:E9,G6:G10,I6:I10)) or =COUNTA(u(u(A1,A3,...,A59),u(C1,C3,...,C59),u(E1,E 3,...,E59))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count unique in non-continuous list
Hi Harlan,
Your function works like a charm! I'm not a wizard with udf's so I'm glad I could solve this with a 'simple' count-function Thanks! Hans Thanks for the feedback "Harlan Grove" wrote: "Bernie Deitrick" <deitbe @ consumer dot org wrote... Hans, Copy the code below, paste it into a regular codemodule, then use it like =UCOUNT((E4:E9,G6:G10,I6:I10)) .... As Charles Williams has pointed out in the past, there can be problems passing multiple area ranges to udfs. http://www.decisionmodels.com/calcsecretsj.htm Besides, this doesn't require udfs if the values in the range are all numeric. Using your example range, try =COUNT(1/FREQUENCY((E4:E9,G6:G10,I6:I10),(E4:E9,G6:G10,I6:I 10))) For mixed text and numbers, more robust to use a udf that accepts a variable number of arguments, though that would fail when one has more than 30 ranges. Most robust would be to use a udf that returns the distinct items in the entries in its argument ranges or arrays or the arguments themselves when they're scalars, then call it nested repeatedly and pass the result through COUNTA. That is, a udf like Function u(ParamArray a() As Variant) As Variant Dim d As Object, x As Variant, y As Variant, z As Variant Set d = CreateObject("Scripting.Dictionary") For Each x In a If TypeOf x Is Range Or IsArray(x) Then For Each y In x If TypeOf y Is Range Then z = y.Value Else z = y If Not d.Exists(z) Then d.Add Key:=z, Item:=1 Next y ElseIf Not d.Exists(x) Then d.Add Key:=x, Item:=1 End If Next x u = d.Keys End Function and call it like so =COUNTA(u(E4:E9,G6:G10,I6:I10)) or =COUNTA(u(u(A1,A3,...,A59),u(C1,C3,...,C59),u(E1,E 3,...,E59))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values from a Subset of a List | Excel Discussion (Misc queries) | |||
how can i count the number of unique names in a list | Excel Discussion (Misc queries) | |||
Charting count of unique dates in a list | Charts and Charting in Excel | |||
count unique items in ever-growing list? | Excel Discussion (Misc queries) | |||
List Unique Occurences and Count | Excel Worksheet Functions |