count unique in non-continuous list

[Hans]

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

 

[Bernie Deitrick]

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

 

[Harlan Grove]

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

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,E3,...,E59)))

 

[Hans]

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