EXCEL
 

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

EXCEL
 

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

EXCEL
 

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

EXCEL
 

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