Results 1 to 5 of 5

Thread: Counting unique values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting unique values

    Hi,

    I have an excel file with two sheets S1, S2 (please see below for sample content). All sheets are in one excel file.

    For each cell in sheet S1, I have to check whether there is/are an/multiple entry/entries in sheet S2. If there are entries, then I would like to count the unique types and its number of occurrences.

    In the example below, in the result sheet, id 123 has 4 rows with the following data
    - 2 times Type A
    - 2 times Type B
    - 1 time Type C
    - 1 time Type D

    Could you guys please help me?

    Thanks and best regards,
    R


    S1's content is as follows:
    123
    124
    125
    126
    127
    128
    129



    S2's content is as follows:
    123 text Type A
    123 text Type B
    123 text Type A
    123 text Type B
    123 text Type C
    123 text Type D
    124 text Type D
    125 text Type D
    125 text Type A



    I expect a result like below in sheet 3 for e.g.:
    123 Type A 2
    123 Type B 2
    123 Type C 1
    123 Type D 1
    124 Type D 1
    125 Type D 1
    125 Type A 1

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,704
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting unique values

    Hi,

    I'd do something like this...

    Sheet2 (A1:D10):

    IDs Description Type ID|Type
    123 text Type A 123|Type A
    123 text Type B 123|Type B
    123 text Type A 123|Type A
    123 text Type B 123|Type B
    123 text Type C 123|Type C
    123 text Type D 123|Type D
    124 text Type D 124|Type D
    125 text Type D 125|Type D
    125 text Type A 125|Type A

    D2 copied down:

    Code:
    =C2&"|"&E2
    Sheet3 (A1:D8):

    Unique ID|Type ID Type Count
    123|Type A 123 Type A 2
    123|Type B 123 Type B 2
    123|Type C 123 Type C 1
    123|Type D 123 Type D 1
    124|Type D 124 Type D 1
    125|Type D 125 Type D 1
    125|Type A 125 Type A 1

    A2 copied down (array entered):

    Code:
    =IF(ROWS(A$2:A2)<=SUMPRODUCT(1/COUNTIF(Sheet2!D$2:D$10,Sheet2!D$2:D$10)),INDEX(Sheet2!D$2:D$10,MATCH(0,COUNTIF(A$1:A1,Sheet2!D$2:D$10),0)),"")
    B2 copied down:

    Code:
    =LEFT(A2,FIND("|",A2)-1)
    C2 copied down:

    Code:
    =RIGHT(A2,LEN(A2)-FIND("|",A2))
    D2 copied down:

    Code:
    =COUNTIFS(Sheet2!A$2:A$10,B2,Sheet2!C$2:C$10,C2)
    The ID|Type helper columns can be hidden if desired.

    Hope this helps.

    Matty

  3. #3
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Counting unique values

    Another option:-
    Data & Results start row 1
    Data on sheet 1 & sheet 2, Results on sheet 3.
    Code:
    Sub MG01Mar40
    Dim Rng As Range, Dn As Range, n As Long
    Dim RngA As Range
    Dim Dic As Object
    Dim Q As Variant, k As Variant, p As Variant, c As Long
    
    With Sheets("Sheet1")
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    End With
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng: .Item(Dn.Value) = Empty: Next Dn
    
    With Sheets("Sheet2")
    Set RngA = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
     End With
     Set Dic = CreateObject("Scripting.Dictionary")
        Dic.CompareMode = 1
       For Each Dn In RngA
            If .exists(Dn.Value) Then
                If Not Dic.exists(Dn.Value) Then
                    Set Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
                End If
            
            If Not Dic(Dn.Value).exists(Dn.Offset(, 2).Value) Then
                    Dic(Dn.Value).Add (Dn.Offset(, 2).Value), 1
            Else
             Q = Dic(Dn.Value).Item(Dn.Offset(, 2).Value)
                Q = Q + 1
             Dic(Dn.Value).Item(Dn.Offset(, 2).Value) = Q
            End If
        End If
        Next Dn
       End With
      
    
    With Sheets("Sheet3")
        For Each k In Dic.Keys
            For Each p In Dic(k)
                   c = c + 1
                    .Cells(c, 1) = k
                    .Cells(c, 2) = p
                    .Cells(c, 3) = Dic(k).Item(p)
            Next p
        
        Next k
    End With
    End Sub
    Regards Mick

  4. #4
    New Member
    Join Date
    Aug 2010
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting unique values

    Thanks Mick and Matty

    I solved it using Pivot tables.

    Thanks for your time and effort.

    BR
    R

  5. #5
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Counting unique values

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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