# Reporting the 'highest' value in a text list

#### mgj

##### New Member
Hi,

At the practice I work at all drawings go through a cycle of revisions. With A-Z being lowest order, then P1-P100, then B1-B100.

In short, a row of data may look like the following:
A,B,C,D,P1,P2,P3,P1,B1,B2,B3,P1

Now, I would like to be able to extract the 'highest' value which in this case would be B3.

I can get Excel (Mac 2008) to find the furthest right value, but that is not always the 'highest' as you can see.

Any help would be much appreciated!

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### T. Valko

##### Well-known Member
Hi,

At the practice I work at all drawings go through a cycle of revisions. With A-Z being lowest order, then P1-P100, then B1-B100.

In short, a row of data may look like the following:
A,B,C,D,P1,P2,P3,P1,B1,B2,B3,P1

Now, I would like to be able to extract the 'highest' value which in this case would be B3.

I can get Excel (Mac 2008) to find the furthest right value, but that is not always the 'highest' as you can see.

Any help would be much appreciated!
Here's one way...

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /><col style="width:29px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">A</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">B</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">C</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">D</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">P1</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">P2</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">P3</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">P1</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">B1</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">B2</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">B3</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">P1</td></tr></table> <br /><br />

You'd have to create a complete list of the values in their ascending value.

<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:35px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >N</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style=" border-style:solid; border-width:1px; border-color:#000000; ">A</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">C</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">P1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">P2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">P3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style=" border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">B3</td></tr></table> <br /><br />

Then this array formula**:

=INDEX(A1:L1,MATCH(MAX(MATCH(A1:L1,N1:N10,0)),MATCH(A1:L1,N1:N10,0),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Assume no empty cells witin your data range.

#### Gary McMaster

##### Well-known Member
It's a bit clunky but maybe it will give you some ideas. I spread it out to make the logic more readable. It can probably be substantially condensed.

It splits the string into the individual codes using the comma for a delimiter. Then it separates the letter from the number for each element. Then it multiplies the ASCII code for the letter by the remaining number while keeping track of the largest product & associated rev code.

The snag, the ASCII code for B is less than P. If it starts with a B and is more than 1 character long, it adds 15 (essentially making the B a Q) before doing the multiplication.

Not absolutely certain it will work under all conditions but it seems like it should. You might also investigate using the "Like" operator.

Hope it helps.

Gary

Code:
``````Public Sub Test()

Dim sText As String
Dim vSplit As Variant
Dim iLBound As Integer
Dim iUBound As Integer
Dim iCount As Integer
Dim iLetterASC As Integer
Dim iNumberValue As Integer
Dim iLetXNum As Integer
Dim iMaxValue As Integer
Dim sHighRev As String

sText = "A , B, C, D, E, Z, D" ', P3, P1, B1, B2, B3, P1"

vSplit = Split(sText, ",")

iLBound = LBound(vSplit)
iUBound = UBound(vSplit)

For iCount = iLBound To iUBound
vSplit(iCount) = Trim(vSplit(iCount))
iLetterASC = Asc(Left(vSplit(iCount), 1))
If iLetterASC = 66 And Len(vSplit(iCount)) > 1 Then
iLetterASC = iLetterASC + 15
End If

iNumberValue = Val(Mid(vSplit(iCount), 2))
If iNumberValue = 0 Then iNumberValue = 1 'No number

iLetXNum = iLetterASC * iNumberValue
If iLetXNum > iMaxValue Then
iMaxValue = iLetXNum
sHighRev = vSplit(iCount)
End If
Next iCount

MsgBox sHighRev

End Sub``````

#### mgj

##### New Member
Thanks for the response guys.

I tried using the index function together with the match function but found the data had to be ordered before the results would be right. Unfortunately the information is a 'live' doc and the data would constantly be updated, up to a maximum of 100 cells.

I thought there might be a method where first the length of the cell could be checked and if 1 char then simply check which is nearest letter Z. Then if the char is 2 char then split the first char off and again see if it's a P,B or T. then look at the 2nd char and see which is the highest number - max function.

I thought I could simplify this by having 4 cell functions.
One looking at those of 1 chars - the A-Z
One looking at those of 2 chars beginning with P - the P1,P2...
One looking at those of 2 chars beginning with B - the B1,B2...
One looking at those of 2 chars beginning with T - the T1,T2...

Does this simply things?

#### T. Valko

##### Well-known Member

Thanks for the response guys.

I tried using the index function together with the match function but found the data had to be ordered before the results would be right. Unfortunately the information is a 'live' doc and the data would constantly be updated, up to a maximum of 100 cells.
The raw data doesn't need to be ordered, just the lookup table.

?????

#### T. Valko

##### Well-known Member
The raw data doesn't need to be ordered, just the lookup table.

?????
Just so we're clear on this...

You create a master lookup table of all the values in ascending order.

You don't need to create a lookup table for each row of values (if that's what you were thinking).

#### mgj

##### New Member
Thanks T.Valko, that works brilliantly!

Is there any way of having it work if there are blank cells? In use a row of data would be filled in from left to right.

If not I may use a wildcard and conditionally format the font as white so it is not seen... although this is pretty crude and bound to go wrong with user input!

Replies
3
Views
71
Replies
0
Views
193
Replies
7
Views
397
Replies
12
Views
612
Replies
7
Views
374