VBA Count maximum occurance of a value in a row

Question

I need to write a macro that searches a specified column and counts all the cells that contain a specified string, such as character "p" and character "q" then associate this in another column i.e the total column, indicating the character which has occurred maximum number of times in the corresponding row
Have attached a sample screen shot of the same. Does anyone have any ideas?

Thank you in advance.

Answer 1

You don't need a macro. The below formula will give you what you need. The range being counted appears in the formula 3 times. You would need to adjust this for the range you want to check

=INDEX(A1:C1,MODE(MATCH(A1:C1,A1:C1,0)))

Note: this will return an error if no single character appears more times than any other. In this case you could wrap the above formula in an IFERROR function to return whatever value you would want to see when this happens.

If you have any blank cells in the row, you can use the following array formula, which adds an IF statement to test for empty cells:

=INDEX(A1:C1,MODE(IF(A1:C1<>"",MATCH(A1:C1,A1:C1,0))))

When entering this formula you will need to press Ctrl + Shift + Enter

Answer 2

Based on your additional criteria of having to exclude certain columns in the row I think it may indeed be easier to use VBA and create a user defined function that you can then enter into the cells in your spreadsheet in the same way as any other function.

I've shown my attempt below which basically checks the column of each cell in the range to ensure it has a header of "Symbol" and if so adds the value of that cell to an Array (after being converted to a number value). There is then another function that gets the mode from that array (this only works on numeric values which is why it was converted in the previous step). Finally that is converted back to a letter.

It's quite a roundabout way and there may be an easier approach but hopefully this will work for now and give you some idea's of how to create these kind of functions for yourself.

Create a new module in your VBA project and copy all 4 of the below procedures into it:

Option Explicit

Public Function MostFrequentValue(RNG As Range) As String
Dim HeaderRow As Integer
Dim a As Range
Dim arr As Variant

HeaderRow = 1 'Change this to whatever row your headers are in

For Each a In RNG.Cells
    If Cells(HeaderRow, a.Column) = "Symbol" Then
        If IsEmpty(arr) Then
            arr = Array(ConvertLetterToNumber(a.Value))
        Else
            ReDim Preserve arr(UBound(arr) + 1)
            arr(UBound(arr)) = ConvertLetterToNumber(a.Value)
        End If
    End If
Next

MostFrequentValue = ConvertNumberToLetter(ArrayMode(arr))

End Function

.

Function ConvertNumberToLetter(ByVal strSource As Integer) As String
ConvertNumberToLetter = LCase(Chr(strSource + 64))
End Function

.

Function ConvertLetterToNumber(ByVal strSource As String) As Integer
Dim i As Integer
Dim strResult As String

strSource = UCase(strSource)
For i = 1 To Len(strSource)
    Select Case Asc(Mid(strSource, i, 1))
        Case 65 To 90:
            strResult = strResult & Asc(Mid(strSource, i, 1)) - 64
        Case Else
            strResult = strResult & Mid(strSource, i, 1)
    End Select
Next
ConvertLetterToNumber = strResult

End Function

.

Function ArrayMode(Ray As Variant) As Integer

With Application
  ArrayMode = .Mode(Ray)
End With
End Function

You would then enter the function into a cell like so =MostFrequentValue("A2:C2")

P.S. This assumes that the symbol in each value in the Symbol column is a lowercase letter of the alphabet (a-z). This appears to be the case from your example