EXCEL

  • Thread starter Thread starter rossmolden
  • Start date Start date
R

rossmolden

I want to display what number appears in the same row the most with the
number 1.
So this example, would be 2 as it appears with 1 in the same row 3 times.

4 1 3 2
2 1
4 3 2
1
2 3 4 1
 
Will all the numbers on each row be unique?

In other words, can you have something like this:

4 1 1
2 2 1 3
 
Ross,

No need to post the same question 4 times - we heard you the first time.

One solution is to use a UDF in VBA. Copy the code below into a regular
codemodule, then use the function like

=MostMatches(1,A1:D5,E1:G1)

Where 1 is the number you are looking to match, A1:D5 is your array of
values, and E1:G1 has the list of possible values: 2, 3, and 4.
If there is a tie, the function will return just the first of the listed
values.

HTH,
Bernie
MS Excel MVP



Function MostMatches(myVal As Integer, _
myIR As Range, _
myP As Range) As Integer

Dim myR As Range
Dim myC() As Integer
Dim i As Integer
Dim j As Integer

ReDim myC(1 To myP.Cells.Count)

For Each myR In myIR.Rows
If Not IsError(Application.Match(myVal, myR, False)) Then
For i = 1 To myP.Cells.Count
If Not IsError(Application.Match( _
myP.Cells(i).Value, myR, False)) Then
myC(i) = myC(i) + 1
End If
Next i
End If
Next myR

j = 1

For i = 2 To myP.Cells.Count
If myC(i) > myC(j) Then j = i
Next i

MostMatches = myP.Cells(j).Value

End Function
 
Bernie,

FWIW, If they are posting using the web interface, it's throwing errors
implying the post was not sent, when it fact it appears to be.

Barb Reinhardt
 
Back
Top