Thanks to Colin Legg I have a formula
IF('DATA 2011-2012'!C3:C2000='Summary 2011-2012'!B7,
IF('DATA 2011-2012'!B3:B2000<>"",MATCH("~"&'DATA 2011-2012'!B3:B2000,'DATA 2011-2012'!B3:B2000&"",0))),
ROW('DATA 2011-2012'!B3:B2000)-ROW('DATA 2011-2012'!B3)+1),1))
This works to provide the following information:
It is trying to count the number of unique names (does not count duplicates) which are on the “Steering Group”.
It looks at my spreadsheet called [Summary 2011-2012] cell B7 (which gives the value “Steering Group”)
Then it looks at my spreadsheet called [Data 2011-2012] specifically at cells c3:c2000 to find any records with the value “Steering Group” whilst at the same time ignoring any duplicated names in located in cells B3:B2000.
The formula works beautifully (even though I don’t understand how).
I now need to know the number of unique names (does not count duplicates) which are on the “Steering Group” (same as the formula above) but which are identified as being in the following age groups:
12 - 17
18 - 30
I have attached the workbook.
Can anyone help me with this additional step?
Thank you in advance.
PS if you believe this cannot be done, then please advice as I will have to rethink the whole way of keeping the data.
I have achieved the following:
"I now need to know the number of unique names (does not count duplicates) which are on the “Steering Group” (same as the formula above) but which are identified as being in the following age groups:
12 - 17
18 - 30
I have achieved what I wanted to do. Not sure if it is as neat as it should be but it does the job. Please see Speadsheet attached.
Basically I added an extra column into the data sheet. I used this column as an exception column to allocate any "Steering Group" data by age group.
Back on the Summary Data Sheet I created the following formula (most of which was kindly provided by Colin Legg):
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:
=C3 &" "& D3
and these formulas in your summary sheet C22 and C23:
=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
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
Set ws = Worksheets("DATA 2011-2012")
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)
k = k + 1
Set ws = Nothing
CountNames = aCounter
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
isDuplicate = tmpBool