Funtion to return the most occuring value

[FryGirl]

I know if Excel there is the MODE.MULT function to return the most occurring value, but that display in separate cells. Does somebody know of a UDF that can return the most occurring separated by a comma? Results in column G

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1, 2[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4, 5, 6[/TD]

</tbody>

Sheet1​

 

[joeu2004]

My version of Excel does not support MODE.MULT. So I cannot test this. But I wonder if the following works for you.

Function myModeMult(r as Range) as String
myModeMult = Join(WorksheetFunction.Mode_Mult(r),",")
End Function

 

[MickG]

Try this:-

Function nMode(Rng [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Lg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, 1
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]For[/COLOR] n = 1 To .Count
Lg = Application.Large(.items(), n)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] .Item(K) = Lg [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not InStr(nstr, K) > 0 [COLOR="Navy"]Then[/COLOR]
            nstr = nstr & IIf(nstr = "", K, ", " & K)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Next[/COLOR] n
nMode = nstr
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[FryGirl]

No, unfortunately that returns #VALUE !

=myModeMult(A1:F1)

I entered it like the above, and also as a CSE

 

[FryGirl]

This works absolutely perfect. Thanks Mick.

 

[MickG]

You're welcome