Find most frequently occuring text in range that consist of multiple rows and colums

user_7959614 0
Viewed 395

My question is closely related to the following topics:

I want to retrieve the text that occurs the most frequent within my range. The problem is that the range consists of multiple rows and/or columns. In the case of a single row / single column, the Excel formula is

=INDEX(D2:D9,MODE(MATCH(D2:D9,D2:D9,0)))

I know that this formula is correct because I get "Inter" as output in cell N2. This is indeed the correct output if I want the most frequent term of Range("D2:D9").

enter image description here

However, I want to find the term #1 of Range("D2:E3"). Incorporating this range in the formula, gives an error. See attachment.

enter image description here

Summarized, what is the correct Excel-formula that shows me the most frequent text of a range that consists of multiple columns and rows? Thank you

excel
2 Answers

=INDIRECT(TEXT(MMULT(CHOOSE({1,2},ROW(MyRange),COLUMN(MyRange))+MOD(ROUNDUP(MATCH(1,0/FREQUENCY(0,1/(1+COUNTIF(MyRange,MyRange))))/COLUMNS(MyRange)^{1,0},0)-1,COLUMNS(MyRange)*ROWS(MyRange)^{1,0}),10^{5;0}),"R0C00000"),0)

Replace MyRange as required.

Note that, if you are not using an English-language version of Excel, parts of the above may require amending (the separators within the array constants - {1,2}, {1,0} and {5;0} - and the part "R0C00000" being two such examples).

Edit: the above is overkill; we can use simply (with CTRL+SHIFT+ENTER):

=INDIRECT(TEXT(MIN(IF(COUNTIF(Rng,Rng)=MAX(COUNTIF(Rng,Rng)),10^5*ROW(Rng)+COLUMN(Rng))),"R0C00000"),0)

Regards

user_33140950

You could try using a UDF. This returns a single value or a comma separated list depending on how many ties there are. I can update for more than 2 columns if required.

Option Explicit

Public Sub Test()
    Dim rng As Range
    Set rng = [D2:E7]

    Debug.Print MaxRepeating(rng)
End Sub

Public Function MaxRepeating(ByVal rng As Range) As String
    Dim arr(), outputArr(), i As Long, counter As Long, dict As Object, maxValue As Long
    Set dict = CreateObject("Scripting.Dictionary")
    counter = 1
    arr = rng.Value
    ReDim outputArr(1 To UBound(arr, 1) + UBound(arr, 2))

    For i = LBound(arr, 1) To UBound(arr, 1)
        dict(arr(i, 1)) = dict(arr(i, 1)) + 1
        dict(arr(i, 2)) = dict(arr(i, 2)) + 1
    Next

    For i = LBound(arr, 1) To UBound(arr, 1)
        If dict(arr(i, 1)) > maxValue Then maxValue = dict(arr(i, 1))
        If dict(arr(i, 2)) > maxValue Then maxValue = dict(arr(i, 2))
    Next

    For i = LBound(arr, 1) To UBound(arr, 1)
        If dict(arr(i, 1)) = maxValue Then
            If IsError(Application.Match(arr(i, 1), outputArr, 0)) Then
                outputArr(counter) = arr(i, 1)
                counter = counter + 1
            End If
        End If
        If dict(arr(i, 2)) = maxValue Then
            If IsError(Application.Match(arr(i, 2), outputArr, 0)) Then
                outputArr(counter) = arr(i, 2)
                counter = counter + 1
            End If
        End If
    Next
    ReDim Preserve outputArr(1 To counter - 1)
    Select Case UBound(outputArr)
    Case 1
        MaxRepeating = outputArr(1)
    Case Else
        MaxRepeating = Join(outputArr, ",")
    End Select
End Function

In sheet:

user_62412350
Related