Reputation: 11

Count Unique Combination from a column in Excel

I have a column like this

this column can consist a combination of number from 0-9

I need to count each unique combination (12=21)

unique combine   count
12354              1
234                2
12                 3
345                2

Upvotes: 1

Answers (3)

Ron Rosenfeld

Reputation: 60174

If you don't have Office 365, you can use Power Query (available in Windows Excel 2010+ as well as Office 365)

Select some cell in your original table
Data => Get&Transform => From Table/Range
When the PQ UI opens, navigate to Home => Advanced Editor
Make note of the Table Name in Line 2 of the code.
Replace the existing code with the M-Code below
Change the table name in line 2 of the pasted code to your "real" table name
Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

M Code

let

//Change name in next line to actual table name in the worksheet
    Source = Excel.CurrentWorkbook(){[Name="theRange"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),

//sort the digits in each string
    #"Added Custom" = Table.AddColumn(#"Changed Type", "sorted strings", 
        each Text.Combine(List.Sort(Text.ToList(Text.From([Column1]))))),

//group by the sorted digit strings
// then generate both a Count and the first occurring digit string for the report
    #"Grouped Rows" = Table.Group(#"Added Custom", {"sorted strings"}, {
        {"Unique Combine", each [Column1]{0}},  
        {"Count", each Table.RowCount(_), Int64.Type}
    }),

//Remove unneeded column
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"sorted strings"})
in
    #"Removed Columns"

Upvotes: 1

Scott Craner

Reputation: 152450

If one has Office 365 then we can use a helper column, where we put the following formula:

=--CONCAT(SORT(MID(A3,SEQUENCE(LEN(A3)),1)))

and drag down. This will create the list in numerical order.

Then using Unique and Filter we get the unique list:

=UNIQUE(FILTER(B:B,B:B<>""))

This will spill and adjust automatically.

Then we use COUNTIFS to do the count:

=COUNTIFS(B:B,D3#)

This will also spill and adjust automatically.

Upvotes: 2

tigeravatar

Reputation: 26640

Something like this should work for you. Adjust wb, ws, rCombine, and rOutput as necessary. I have commented the code to try to make it easier to understand.

Sub tgr()
    
    'Define where data is kept
    Dim wb As Workbook:     Set wb = ActiveWorkbook
    Dim ws As Worksheet:    Set ws = wb.ActiveSheet
    Dim rCombine As Range:  Set rCombine = ws.Range("A2", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    Dim rOutput As Range:   Set rOutput = ws.Range("C2")
    If rCombine.Row < 2 Then Exit Sub   'No data
    
    'Clear previous results
    rOutput.CurrentRegion.Offset(1).ClearContents
    
    'Prepare variables for looping and evaluating unique combinations
    Dim hUnqCombine As Object:  Set hUnqCombine = CreateObject("Scripting.Dictionary")
    Dim aTemp() As Variant
    Dim vCombo As Variant
    Dim lCheckSum As Long
    Dim i As Long
    
    'Loop over the combinations
    For Each vCombo In rCombine.Value
        'Verify this combo is numeric and not blank
        If IsNumeric(vCombo) And Len(vCombo) > 0 Then
            'Generate a unique checksum for this combo
            'This will ensure that 12 is treated the same as 21
            lCheckSum = 0
            For i = 1 To Len(vCombo)
                lCheckSum = lCheckSum + 2 ^ (--Mid(vCombo, i, 1))
            Next i
            
            'Check if this unique checksum already exists
            If hUnqCombine.Exists(lCheckSum) Then
                'Combination has been encountered before, increase the count
                aTemp = hUnqCombine(lCheckSum)
                aTemp(2) = aTemp(2) + 1
                hUnqCombine(lCheckSum) = aTemp
                Erase aTemp
            Else
                'Combination has not been encountered before, create entry
                ReDim aTemp(1 To 2)
                aTemp(1) = vCombo
                aTemp(2) = 1
                hUnqCombine(lCheckSum) = aTemp
                Erase aTemp
            End If
        End If
    Next vCombo
    
    'Verify that there are results to output
    If hUnqCombine.Count > 0 Then
        'Prepare variables to convert the hash of combinations to output array
        Dim aResults() As Variant:  ReDim aResults(1 To hUnqCombine.Count, 1 To 2)
        Dim vKey As Variant
        i = 0
        
        'Loop over each unique combination and convert to an array for output
        For Each vKey In hUnqCombine.Keys
            i = i + 1
            aResults(i, 1) = hUnqCombine(vKey)(1)
            aResults(i, 2) = hUnqCombine(vKey)(2)
        Next vKey
        
        'Output the results
        rOutput.Resize(UBound(aResults, 1), UBound(aResults, 2)).Value = aResults
    End If
    
End Sub

Upvotes: 1

Count Unique Combination from a column in Excel

Answers (3)

Related Questions