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


--
Biff
Microsoft Excel MVP


"rossmolden" wrote in message
...
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

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


"rossmolden" wrote in message
...
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

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


"Bernie Deitrick" wrote:

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


"rossmolden" wrote in message
...
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