Stephen_IV
Well-known Member
- Joined
- Mar 17, 2003
- Messages
- 1,171
- Office Version
- 365
- 2019
- Platform
- Windows
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.
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 |
VBA Code:
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