Not sure about your counts. For Steering Group 18 - 30, I count 10. If I am
correct then I have an alternate solution.
You have added a formula to your data sheet in column E. I replaced your formula in column E with this:
and these formulas in your summary sheet C22 and C23:
Code:
=CountNames("Steering Group 18 - 30")
=CountNames("Networking Session Over 30")
And with this code in a module that searches through the names and
returns a count of unique names for the parameter that you send to
the function.
Code:
Function CountNames(dataKey As String) As Integer
Dim ws As Worksheet
Dim k As Long, aCounter As Long
Dim arrayText() As String, pName As String
ReDim arrayText(1)
Set ws = Worksheets("DATA 2011-2012")
With ws
k = 3
Do While .Cells(k, 2).Value <> ""
If .Cells(k, 5).Value = dataKey Then
pName = .Cells(k, 2).Value
If Not isDuplicate(pName, arrayText) Then
arrayText(aCounter) = pName
aCounter = aCounter + 1
ReDim Preserve arrayText(aCounter + 1)
End If
End If
k = k + 1
Loop
End With
Set ws = Nothing
CountNames = aCounter
End Function
Function isDuplicate(txtValue, ByRef arrayText) As Boolean
Dim i As Integer
Dim tmpBool As Boolean
For i = 0 To UBound(arrayText) - 1
If arrayText(i) = txtValue Then
tmpBool = True
Exit For
End If
Next
isDuplicate = tmpBool
End Function
I hope this helps.
dBforums
>
Counting Unique Values by Grouping
11-11-11, 07:32
Linear Mode
