Thread: Find which group of number repeats the most

Hi Showson,

Welcome to the forum.

Just as an option, have 4th column as well and where add the entries on left:-

column 4
6
12
6
13
6


Now you can easily see that 6 is appearing most hence rows where 6 is there as total is actually having most frequently repeating pattern. Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

Try this user defined function.

Option Explicit

Function GetMaxSortedCombination(MyRange As Range) As String
Dim MyRowArray()
Dim MyResultArray()
Dim N As Long, M As Long, X As Long, Y As Long, Z As Long
Dim TempValue As String
Dim RowSortedCombination As String
Dim CountArray()
Dim Found As Boolean
Dim GetMaxElement As Long

ReDim MyRowArray(MyRange.Columns.Count - 1)
ReDim MyResultArray(MyRange.Rows.Count - 1)

For X = 0 To UBound(MyResultArray)

    For N = 0 To UBound(MyRowArray)
        MyRowArray(N) = MyRange(X + 1, N + 1).Value
    Next N
    
    For N = 0 To UBound(MyRowArray)
        For M = N + 1 To UBound(MyRowArray)
            If MyRowArray(N) < MyRowArray(M) Then
                TempValue = MyRowArray(N)
                MyRowArray(N) = MyRowArray(M)
                MyRowArray(M) = TempValue
            End If
        Next M
    Next N
    
    RowSortedCombination = ""
    For N = 0 To UBound(MyRowArray)
        RowSortedCombination = RowSortedCombination & MyRowArray(N)
    Next N
    
    MyResultArray(X) = RowSortedCombination
Next X
 

ReDim CountArray(1, 0)
For X = 0 To UBound(MyResultArray)
    Found = False
    For Y = 0 To UBound(CountArray, 2)
        If CountArray(0, Y) = MyResultArray(X) Then
            CountArray(1, Y) = CountArray(1, Y) + 1
            Found = True
            Exit For
        End If
    Next Y
    If Found = False Then
        If CountArray(0, 0) <> "" Then
            ReDim Preserve CountArray(1, UBound(CountArray, 2) + 1)
            CountArray(0, UBound(CountArray, 2)) = MyResultArray(X)
            CountArray(1, UBound(CountArray, 2)) = 1
        Else
            CountArray(0, 0) = MyResultArray(X)
            CountArray(1, 0) = 1
        End If
    End If
Next X

GetMaxElement = 0
For Z = 1 To UBound(CountArray, 2)
    If CountArray(1, Z) > CountArray(1, GetMaxElement) Then GetMaxElement = Z
Next Z

GetMaxSortedCombination = CountArray(0, GetMaxElement)
End Function

Paste this into a new module in the VBA editor (Alt F11) and use the formula like =GetMaxSortedCombination(A1:C27) in your sheet.