Pick the highest in the range

[Ruba]

My question would help me in solving a transport issue. There are three people coming from differnt places and travelling in the same cab. X is 15km away from work, Y is 10 km away, and Z is 5 km away. X is the first pickup and the cab driver would be given allowance basing on the maximum km pickup..which shud be X.

X 15

Y 10

Z 5


i have all the three names in one cell and Xcel should be able to recognise and populate the person who lives at the maximum distance in the corresponding cell, here it should be X in the next cell.


XYZ X


Is this possible.

 

[Luke M]

If column A is the names, and col B is the distances, the name of farthest distance is:

=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

 

[Luke M]

Re-reading this, I see you may be asking a different, and somewhat harder question.


I'm guessing you have a much larger list of names and distances, and what you want to do is in a single cell list a few of these names, and have a formula look at those names and give the name of farthest rider. Is this correct?


Clarification needed: Are the names truly single characters/letters? Are they seperated by anything (comma, space, etc) or do they just run together?

 

[Ruba]

You are right Luke. I have a huge list of Names and the distances. They are names separated by commas.

Ex: John, Paul, Chandu. all three names are in one cell. All three are taking the same cab to work. the first pickup would be Chandu who is at a distance of 50 km. the second pickup is Paul who lives at a distance of 30km and the last pickup is John who is 10 km away.


There would be a master sheet with the details of the employees and the distances to work separately.


So Excel needs to consider the three travelling together in one cab and generate the farthest km in the corresponding cell. Hope i am clear now.


thanks again

 

[Luke M]

Ruba,


Because of the need to store multiple values and do comparisons, this was easier (for me) to build a UDF. To install, copy this function, open your VBE (Alt+F11), insert, Module, paste this in. Then in your worksheet, you can input a formula described below.


UDF:

Function MaxString(xList As String, xTable As Range, Optional Delim As String) As String

Dim WordCount As Integer

'Can store up to 30 names, arbitrarily set

Dim xWord(1 To 30) As Variant

Dim xValue(1 To 30) As Variant

Dim MaxValue As Long

Dim i As Integer


If Delim = Empty Then Delim = ", "


WordCount = (Len(xList) - Len(Replace(xList, Delim, ""))) / Len(Delim) + 1


'Find all the names

For i = 1 To WordCount - 1

xWord(i) = Left(xList, InStr(1, xList, Delim) - 1)

xList = Mid(xList, InStr(1, xList, Delim) + Len(Delim))

Next

xWord(WordCount) = xList


'Find all the values

For i = 1 To WordCount

xValue(i) = WorksheetFunction.VLookup(xWord(i), xTable, 2, False)

MaxValue = WorksheetFunction.Max(MaxValue, xValue(i))

Next


For i = 1 To WordCount

If xValue(i) = MaxValue Then

MaxString = xWord(i)

Exit For

End If

Next


End Function


In the workbook, your formula will be something like:

=MaxString(C2,A2:B10,", ")


Explained:

The first arguement is the cell with the string of names. The 2nd arguement is the table with names in first column, values in 2nd column. The last arguement is optional, it tells the formula what deliminator you are using. In this case, your using ", ". If you don't specify a deliminator, the formula will assume it's ", ".


Cautions:

If you give a name that's not in the list, error out.

If you don't do all the deliminators the same, error out.