SORT A LIST OF NUMBERS AND LETTERS A,B,C,D AND FIND MAX
 

I have a drawing register that lists drawing numbers and the last time it was
issued. Initially the drawing is issued with a numbers, however as the
project continues, the number changes to a letter, eg A, then B, C etc. If
the drg has been issued as a number I want to extract the largest number,
however if its a letter, then i want to find the latest letter eg

a particular drg may be issued
S1001 1 2 3 4 5 A B C so i want to be able to extract C
S1002 1 2 3 so i want to be able to extract 3
S1003 A B C D E so i want to be able to extract E

its ok if there are only a couple of drawings, but 500+ drgs takes a while
to sort out

SORT A LIST OF NUMBERS AND LETTERS A,B,C,D AND FIND MAX
 

There may be a simpler solution, but if your data is in B1:I1 and assuming
your numbers could be = 10, try:
=IF(SUM(--ISTEXT(B1:I1)),CHAR(MAX(IF(B1:I1<"",CODE(B1:I1)," "))),MAX(B1:I1))

If your numbers will always be < 10, try shortening it to:
=CHAR(MAX(IF(B1:I1<"",CODE(B1:I1),""))

Both are array entered (entered with Control+Shift+Enter, not just the enter
key)

"Darryl" wrote:

I have a drawing register that lists drawing numbers and the last time it was
issued. Initially the drawing is issued with a numbers, however as the
project continues, the number changes to a letter, eg A, then B, C etc. If
the drg has been issued as a number I want to extract the largest number,
however if its a letter, then i want to find the latest letter eg

a particular drg may be issued
S1001 1 2 3 4 5 A B C so i want to be able to extract C
S1002 1 2 3 so i want to be able to extract 3
S1003 A B C D E so i want to be able to extract E

its ok if there are only a couple of drawings, but 500+ drgs takes a while
to sort out

SORT A LIST OF NUMBERS AND LETTERS A,B,C,D AND FIND MAX
 

It looks like you'd like to return the last value in the row. If so,
try...

=LOOKUP(2,1/(Range<""),Range)

Hope this helps!

In article ,
Darryl wrote:

I have a drawing register that lists drawing numbers and the last time it was
issued. Initially the drawing is issued with a numbers, however as the
project continues, the number changes to a letter, eg A, then B, C etc. If
the drg has been issued as a number I want to extract the largest number,
however if its a letter, then i want to find the latest letter eg

a particular drg may be issued
S1001 1 2 3 4 5 A B C so i want to be able to extract C
S1002 1 2 3 so i want to be able to extract 3
S1003 A B C D E so i want to be able to extract E

its ok if there are only a couple of drawings, but 500+ drgs takes a while
to sort out