Finding the most occurrences of a value in a data set, using VBA.

[Technology]

With this data starting at A1, how would we go about finding which column has the most occurrences of 0's. Using VBA function, place the result in A25.

7	3	8	6	3	6
1	6	9	4	2	5
5	10	10	4	6	2
1	7	6	7	0	6
1	5	6	3	9	7
1	7	8	8	1	6
2	8	0	6	0	2
7	5	10	1	3	0
8	5	9	9	4	0
6	4	3	2	4	2
8	4	7	6	6	9
6	4	1	4	3	1
2	3	0	8	8	4
0	3	5	4	7	6
7	1	0	0	8	3
4	1	8	3	9	6
4	1	1	10	3	5
7	3	0	4	1	4
3	6	6	9	6	1
8	2	3	6	6	8
0	0	1	10	1	2
5	2	9	4	3	7
5	7	3	8	1	6

 

[Peter_SSs]

What if multiple columns have the same maximum number of zeros? Is that possible and if so, how should the result(s) be presented?

 

[Technology]

Result for each column can be presented even its repeated. The desired result is the column with the most 0's. I believe that column C with 3, 0's.

 

[Zot]

Try this. Select range and run macro

Sub FindMostFreq()

Dim MaxCount As Long, nCount As Long
Dim MaxVal As Variant, xVal As Variant
Dim cell As Range, rngNum As Range
Dim dict As Object

Set dict = CreateObject("Scripting.Dictionary")
Set rngNum = Selection

MaxCount = 0
MaxVal = ""
For Each cell In rngNum
    If Not cell.Value2 = "" Then
        xVal = cell.Value2
        dict(xVal) = dict(xVal) + 1
        nCount = dict(xVal)
        If nCount > MaxCount Then
            MaxCount = nCount
            MaxVal = xVal
        End If
    End If
Next
MsgBox "Most occurance : " & MaxVal & " occurs " & MaxCount & " times"

End Sub

opps. I did not read your requirement properly

 

[Peter_SSs]

Result for each column can be presented even its repeated. The desired result is the column with the most 0's. I believe that column C with 3, 0's.

Column C actually has 4 0's. However, your answer about multiples is still not clear. Suppose columns A, C and F all had four 0's.
Should cell A25 contain "A, C, F"
or A25 holds "A", B25 holds "C" and C25 holds "F"
or something else?

 

[Technology]

@Peter_SSs
Ok, I get what your say. My bad on the counting, as well. A25 holds "A", B25 holds "C" and C25 holds "F" would work perfect.

 

[Peter_SSs]

A25 holds "A", B25 holds "C" and C25 holds "F" would work perfect.

Give this a try with a copy of your workbook.

Sub Max_Zeros()
  Dim maxVal As Long, c As Long
  Dim s As String
  Dim cols As Variant
  
  With Range("A25:F25")
    .Formula = "=COUNTIF(A1:A23,0)"
    maxVal = Application.Max(.Value)
    For c = 1 To .Columns.Count
      If .Cells(c).Value = maxVal Then s = s & " " & Chr(64 + c)
    Next c
    .ClearContents
    cols = Split(Mid(s, 2))
    .Resize(, UBound(cols) + 1).Value = cols
  End With
End Sub

 

[Technology]

Give this a try with a copy of your workbook.

Sub Max_Zeros()
  Dim maxVal As Long, c As Long
  Dim s As String
  Dim cols As Variant
 
  With Range("A25:F25")
    .Formula = "=COUNTIF(A1:A23,0)"
    maxVal = Application.Max(.Value)
    For c = 1 To .Columns.Count
      If .Cells(c).Value = maxVal Then s = s & " " & Chr(64 + c)
    Next c
    .ClearContents
    cols = Split(Mid(s, 2))
    .Resize(, UBound(cols) + 1).Value = cols
  End With
End Sub

The following only states the column's in row 25, instead of which column has the most 0's.

 

[Peter_SSs]

Here is may sample data (I changed a few of the values so multiple columns had the max number of 0's [4]) and the results of the code.
I have just highlighted the 0 values to make checking easier.
Is this the result you would want for this data?

Technology.xlsm
	A	B	C	D	E	F
1	7	3	8	6	3	6
2	1	6	9	4	2	5
3	5	10	10	4	6	2
4	1	7	6	7	0	6
5	1	5	6	3	9	7
6	1	7	8	8	1	6
7	2	8	0	6	0	2
8	0	5	10	1	3	0
9	0	5	9	9	4	0
10	6	4	3	2	4	2
11	8	4	7	6	6	9
12	6	4	1	4	3	1
13	2	3	0	8	8	0
14	0	3	5	4	7	0
15	7	1	0	0	8	3
16	4	1	8	3	9	6
17	4	1	1	10	3	5
18	7	3	0	4	1	4
19	3	6	6	9	6	1
20	8	2	3	6	6	8
21	0	0	1	10	1	2
22	5	2	9	4	3	7
23	5	7	3	8	1	6
24
25	A	C	F
Sheet1

 

[Technology]

Here is may sample data (I changed a few of the values so multiple columns had the max number of 0's [4]) and the results of the code.
I have just highlighted the 0 values to make checking easier.
Is this the result you would want for this data?

Technology.xlsm
	A	B	C	D	E	F
1	7	3	8	6	3	6
2	1	6	9	4	2	5
3	5	10	10	4	6	2
4	1	7	6	7	0	6
5	1	5	6	3	9	7
6	1	7	8	8	1	6
7	2	8	0	6	0	2
8	0	5	10	1	3	0
9	0	5	9	9	4	0
10	6	4	3	2	4	2
11	8	4	7	6	6	9
12	6	4	1	4	3	1
13	2	3	0	8	8	0
14	0	3	5	4	7	0
15	7	1	0	0	8	3
16	4	1	8	3	9	6
17	4	1	1	10	3	5
18	7	3	0	4	1	4
19	3	6	6	9	6	1
20	8	2	3	6	6	8
21	0	0	1	10	1	2
22	5	2	9	4	3	7
23	5	7	3	8	1	6
24
25	A	C	F
Sheet1

Thank you, I would prefer that the code stated that just C based on the data had the most zeros compared to the other columns. Maybe having a count of each column then the code selecting the highest value. I'm not sure the best way of solving this one but I just want to say that I really appreciate your help. I'm definitely learning.