Reporting the 'highest' value in a text list

mgj

New Member
Joined
Apr 5, 2011
Messages
3
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!
 

Some videos you may like

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
Joined
May 9, 2009
Messages
16,623
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...

With your data:

<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
Joined
Feb 8, 2009
Messages
1,977
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
Joined
Apr 5, 2011
Messages
3
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
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

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
Joined
May 9, 2009
Messages
16,623
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
Joined
Apr 5, 2011
Messages
3
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top