Reporting the 'highest' value in a text list

[mgj]

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!

 

[T. Valko]

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:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	B	C	D	P1	P2	P3	P1	B1	B2	B3	P1
Sheet1

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

Book1
	N
1	A
2	B
3	C
4	D
5	P1
6	P2
7	P3
8	B1
9	B2
10	B3
Sheet1

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]

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

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]

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]

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]

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]

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!