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

    Unanswered: counting unique values - Part 2

    Hi I just cant seem to work this out. If I am not making myself clear enough please advise.

    The following works if I just want to count the number of cells which contains a particular detail located on a seperate spreadsheet.

    =COUNTIF('YWLP DATA 2011-2012'!$C$3:$C$2000,'YWLP Summary 2011-2012'!B7)

    But the cells in c3:c2000 contain duplicate entries and I want it to count only unique values.

    How can this be performed?

    For example in the following data Tessa Hen and Nicole Daill are mentioned twice.The data is found on a sheet called 'YWLP DATA 2011-2012' and the field which I am referencing is on the 'YWLP Summary 2011-2012'. I want to look up the 'YWLP Summary 2011-2012' summary sheet cell B7 which for this example contains the words "steering group". I want to count the number of unique names which are allocated to "Steering Group". In this example 11 names are allocated to the steering group but only 9 names are unique.

    Malisa Kli Group1
    Samantha Kli Group1
    Nicole Daill Steering Group
    Tessa Hen Steering Group
    Carla Tabot Steering Group
    Danielle Big Steering Group
    Emma Holiday Steering Group
    Francesca House Steering Group
    Don Kli Group1
    Jess Chad Steering Group
    Katie Berg Steering Group
    Malisa Klicke Steering Group
    Nicole Daill Steering Group
    Tessa Hen Steering Group
    Mel Thompson Group1


    Thanks in advance for any help KD
    Last edited by Karen Day; 11-07-11 at 16:55.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    An array formula like this will do the job:
    Code:
     
    =SUM(IF(FREQUENCY(
        IF('YWLP DATA 2011-2012'!D3:D2000='YWLP Summary 2011-2012'!B7,
        IF('YWLP DATA 2011-2012'!C3:C2000<>"",MATCH("~"&'YWLP DATA 2011-2012'!C3:C2000,'YWLP DATA 2011-2012'!C3:C2000&"",0))),
        ROW('YWLP DATA 2011-2012'!C3:C2000)-ROW('YWLP DATA 2011-2012'!C3)+1),1))
    It's an array formula so when you type it into the formula bar, you must complete the entry with CTRL+SHIFT+ENTER, not just the usual ENTER. If done correctly, Excel will automatically surround the formula with curly braces { }.

    Alternatively you could write a little VBA UDF to do it (particularly handy if someone's already written it for you).

  3. #3
    Join Date
    May 2002
    Posts
    157
    Hi Colin,
    I want to thank you very much for the time you have taken to help me.

    Unfortunately I cannot seem to get either the formula array or the VBA code to work.

    I have attached the spreadsheet and put two comments in the spreadsheet.

    I hope you can assist me again.

    Thanks again.

    KD
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Here is VBA code that does the unique count you are looking for. The function checks for the specific category under Activity, and if the participant name is not already in an array that stores the participant names, the name is added to the array and the counter is incremented. Even if you don't use this, it was an interesting exercise.
    Code:
    Function Count_Unique(strCategory) As Integer
    'returns a unique count of participant names for the specific category
    Dim arrayTxt()
    Dim strPartName As String, strCat As String
    Dim ws As Worksheet
    Dim counter As Integer
    ReDim arrayTxt(1)
    
        Set ws = Worksheets("DATA 2011-2012")
        With ws
            i = 3
            arrayTxt(0) = ""
            Do While ws.Cells(i, 3) <> ""
                strCat = ws.Cells(i, 3)
                If strCat = strCategory Then
                    strPartName = ws.Cells(i, 2)
                    If Not isDuplicate(strPartName, arrayTxt) Then
                        arrayTxt(counter) = strPartName
                        counter = counter + 1
                        ReDim Preserve arrayTxt(counter + 1)
                    End If
                End If
                i = i + 1
            Loop
        End With
    
        Set ws = Nothing
        
        Count_Unique = counter
          
    End Function
    
    Function isDuplicate(strName, ByRef arrayTxt()) As Boolean
    'return True if value in strName is found in the array
    Dim j As Integer, tmpBool As Boolean
    
        For j = 0 To UBound(arrayTxt) - 1
            If strName = arrayTxt(j) Then
                tmpBool = True
                Exit For
            End If
        Next
        
        isDuplicate = tmpBool
        
    End Function
    In cell B7:
    Code:
    =Count_Unique(B7)

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Karen,

    Based on the spreadsheet you attached, you have to change the sheet names and the column references in the formula. It becomes:
    Code:
    =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))
    which returns a result of 10. I have attached your worksheet with the formula in it for you. It is still an array formula.



    Hi Jerry,

    Nice work. I'd recommend that you pass the range which must contain "Steering Group" and the literal "Steering Group" into the function as arguments, otherwise you'll have problems with Excel's dependency tree.
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Colin, I'm here to learn. Do you mean that instead of using "B7" in the cell
    because of row inserts and deletes that might cause Excel to revise the
    formula?

    You say "pass the range...into the function...", could you expand on that a little?
    Knowing that two specific columns are involved, why is it a better solution to
    pass the range than to just refer to the two columns in code?

    Thanks.
    Jerry

  7. #7
    Join Date
    May 2002
    Posts
    157
    Hi all,

    Thank you so much for the assistance provided.
    I really appreciate the assistance.

    Colin, thanks so much it works perfectly, but you knew that already.

    I wonder if you could help me a bit further on this spreadsheet?
    It is once again attached.

    My next problem is an extension of what Colin has already resolved for me.

    So far Colin has helped me with counting the number of participants who are on the Steering Group EXCLUDING duplicates.

    My next step is to count the number of participants who are on the Steering Group EXCLUDING duplicates and in age groups:
    12 - 17
    18 - 30
    Over 30

    My next step after that is to count the number of participants who are on the Steering Group EXCLUDING duplicates and by culture. But I think I will be able to do this if I know how to do the above.


    Hoping someone can help
    Karen Day
    Attached Files Attached Files
    Last edited by Karen Day; 11-10-11 at 09:00.

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

    Regarding the 2nd request for assistance. I haven't had a reply.
    Is this something that just can't be done and so no-one is answering?
    Regards
    Karen Day

  9. #9
    Join Date
    May 2002
    Posts
    157
    For an answer to the above see "Counting Unique Values by Grouping" submitted by me.

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Karen,

    Sorry for the slow reply, I've been really busy.

    You're using XL 2007 or later, so the multiple condition count formula in G20:G22 can be simplified from SUMPRODUCT to COUNTIFS. COUNTIFS is more efficient than SUMPRODUCT.
    Code:
    =COUNTIFS('DATA 2011-2012'!$C$3:$C$2000,"Steering Group",
        'DATA 2011-2012'!$D$3:$D$2000,B20)

    My next step is to count the number of participants who are on the Steering Group EXCLUDING duplicates and in age groups
    You can extend the formula I gave you earlier to take into account this extra condition.

    So the earlier formula was this:
    Code:
    =SUM(IF(FREQUENCY(
        IF('DATA 2011-2012'!$C$3:$C$2000='Summary 2011-2012'!$B$7,
        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))
    To extend it with the extra condition you do this:
    Code:
    =SUM(IF(FREQUENCY(
        IF('DATA 2011-2012'!$C$3:$C$2000='Summary 2011-2012'!$B$7,
        IF('DATA 2011-2012'!$B$3:$B$2000<>"",
       IF('DATA 2011-2012'!$D$3:$D$2000='Summary 2011-2012'!$B20,
        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))
    Attached Files Attached Files

Posting Permissions

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