Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Counting Unique Values by Grouping

    Hi there
    Thanks to Colin Legg I have a formula
    =SUM(IF(FREQUENCY(
    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
    Over 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.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2002
    Posts
    157
    Hi everyone,

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

    =(COUNTIF('DATA 2011-2012'!$D$3:$D$2000,B21))
    -(SUMPRODUCT(('DATA 2011-2012'!$C$3:$C$2000="Steering Group")*('DATA 2011-2012'!$D$3:$D$2000=B21)))
    +SUM(IF(FREQUENCY(
    IF('DATA 2011-2012'!$E$3:$E$2000='Summary 2011-2012'!B21,
    IF('DATA 2011-2012'!$B$3:$B$2000<>"",MATCH("~"&'DATA 2011-2012'!$B$3:$B$2000,'DATA 2011-2012'!$B$3:$B$2000&"",0))),
    ROW('DATA 2011-2012'!$B$3:$B$2000)-ROW('DATA 2011-2012'!$B$3)+1),1))

    This might be 'dirty' but it does the job.

    If you can think of a better way of doing it, then please let me know.

    Regards

    Karen Day
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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:
    Code:
    =C3 &" "& D3
    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.

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Oops, the counts I looked at were Age section starting on row #20. Your count of 10 on row 15 is correct. So the question still is "can your formula solution be done in any simpler way?"
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •