Counting number of times a combination appears in different rows

James01

Board Regular
Joined
Oct 29, 2008
Messages
124
Hi

I have data in column A which looks like below

54
521
87454
521

Here is what I am trying to determine

1 appears with 1 in 0 rows
1 appears with 2 in 2 rows
1 appears with 3 in 0 rows
1 appears with 4 in 0 rows
1 appears with 5 in 2 rows

and so on.

I can have the table setup in different column as

Value 1, Value 1, Count
Value 1, Value 2, Count

etc

Hope this makes sense
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
This code might do the trick:

Code:
Option Explicit
Const OUTPUT_RANGE = "H1"
Dim combo(10, 10) As Long
Sub CountCombinations()
    Dim r As Range
    Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    r.Select
    Dim i As Integer, j As Integer
    For i = 0 To 9
        Dim cell As Range
        For Each cell In r.Cells
            If InStr(cell.Value, i) > 0 Then
                For j = 0 To 9
                    If j <> i Then
                        If InStr(cell.Value, j) > 0 Then combo(i, j) = combo(i, j) + 1
                    End If
                Next j
            End If
        Next cell
    Next i
 
    DisplayResults_Abbreviated
 
End Sub
Sub DisplayResults()
    Dim i As Integer, j As Integer
    Dim iRow As Integer
    Columns(Left(OUTPUT_RANGE, 1)).Clear
 
    For i = 0 To 9
        For j = 0 To 9
            If i <> j Then
                Range(OUTPUT_RANGE).Offset(iRow).Value = i & " appears with " & j & " in " & combo(i, j) & " rows"
                iRow = iRow + 1
            End If
        Next j
        iRow = iRow + 1
    Next i
End Sub
Sub DisplayResults_Abbreviated()
    Dim i As Integer, j As Integer
    Dim iRow As Integer
    Columns(Left(OUTPUT_RANGE, 1)).Clear
 
    For i = 0 To 9
        For j = 0 To 9
            If i <> j Then
                If combo(i, j) <> 0 Then
                    Range(OUTPUT_RANGE).Offset(iRow).Value = i & " appears with " & j & " in " & combo(i, j) & " rows"
                    iRow = iRow + 1
                End If
            End If
        Next j
        iRow = iRow + 1
    Next i
End Sub

You can choose between two different ways of displaying the results, complete (DisplayResults_Complete) and abbreviated (DisplayResults_Abbreviated), the latter only displays the combinations that show up in at least one row.

Hope this helped,
Rolf
 
Upvote 0
may be this,
Excel Workbook
A
154
2521
387454
4521
Sheet1
Excel 2003
Excel Workbook
CDE
1N1N2Count
2110
3122
4130
5141
6152
7160
8170
9180
10190
Sheet1
Excel 2003
Cell Formulas
RangeFormula
E2=SUMPRODUCT(--((LEN($A$1:$A$4)-LEN(SUBSTITUTE($A$1:$A$4,$C2,"")))+(LEN($A$1:$A$4)-LEN(SUBSTITUTE($A$1:$A$4,D2,"")))>(1+($C2=D2))))
 
Upvote 0

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,094
Latest member
teemeren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top