VBA Function Find Freq Max

[Stephen_IV]

Good afternoon,
I have a function that someone gave me, it looks for the most names that occur in a row or column. It works fine except when there's a tie. In the example below Mary and Jean occur 6 times. I would like to output to show
Mary, Jean
Any help would be appreciated.

VBA Multi Max Dictionary.xlsm
	A
1	AL
2	Joe
3	Mary
4	Jean
5	Jack
6	Mary
7	Mary
8	John
9	Jean
10	Jean
11	AL
12	Joe
13	Mary
14	Jean
15	Jack
16	Mary
17	Mary
18	John
19	Jean
20	Jean
Sheet2

Function FreqMax(r As Range)
Set dic = CreateObject("scripting.dictionary")
On Error Resume Next
Mmax = 0
x = ""
    For Each xcell In r
        If xcell.Value <> "" Then
            dic(xcell.Value) = dic(xcell.Value) + 1
            If dic(xcell.Value) > Mmax Then
                Mmax = dic(xcell.Value)
                x = xcell.Value
            End If
        End If
    Next
FreqMax = x
End Function

 

[Fluff]

Hi what version of Excel are you using?
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Stephen_IV]

Hi what version of Excel are you using?
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sorry about that, I updated the info.

 

[Bo_Ry]

Please try

Array formula for a single column.
=TEXTJOIN(", ",,INDEX(A1:A20,MODE.MULT(MATCH(A1:A20,A1:A20,))))

or UDF

Function FreqMax(r As Range)
Dim rn As Range, dic As Object, mx As Long, a, t As String
Set dic = CreateObject("scripting.dictionary")
    For Each rn In r
        If rn <> "" Then
            dic(rn.Value) = dic(rn.Value) + 1
            mx = Application.Max(dic(rn.Value), mx)
        End If
    Next
    For Each a In dic.keys
        If dic(a) = mx Then t = t & ", " & a
    Next
FreqMax = Mid(t, 3)
End Function

Book1
	A	B	C	D
1	AL	AL		Mary, Jean
2	Joe	Joe		Mary, Jean
3	Mary	Mary
4	Jean	Jean		Mary, Jean
5	Jack	Jack
6	Mary	Mary
7	Mary	Mary
8	John	John
9	Jean	Jean
10	Jean	Jean
11	AL
12	Joe
13	Mary
14	Jean
15	Jack
16	Mary
17	Mary
18	John
19	Jean
20	Jean
Sheet1

Cell Formulas
Range	Formula
D1	D1	=TEXTJOIN(", ",,INDEX(A1:A20,N(IF(1,MODE.MULT(MATCH(A1:A20,A1:A20,))))))
D2	D2	=TEXTJOIN(", ",,INDEX(A1:A20,MODE.MULT(MATCH(A1:A20,A1:A20,))))
D4	D4	=FreqMax(A1:B20)

 

[Stephen_IV]

Beautiful!!! Thank you very much! I really appreciate it!!!!