Get the highest value when all values consist of letters AND numbers

[LinaVa]

Hi,

I have thousands of drawings listed in colums and each in the row has revisions P01 to P10 for Proposal and C01 to C10 For Construction.
Is there a way of getting the highest revision of all listed in a row without turning them into numbers when P is older than C , but 1 is older than 10?
MAX function doesnt count when value consists of both - number and letter.

Please help.... Manualy selecting the highes revision will cost me weeks... and brain damage.. as I have thousands of drawings and more are coming...

Thank you!
Lina

[TMS]

You can use an Array Formula:

=MAX(IF($A$2:$A$100<>"",--RIGHT($A$2:$A$100,LEN($A$2:$A$100)-1)))

Committed with Ctrl-Shift-Enter rather than just Enter.

Adjust the range as necessary.

You will see:

{=MAX(IF($A$2:$A$100<>"",--RIGHT($A$2:$A$100,LEN($A$2:$A$100)-1)))}

Regards, TMS

[TMS]

Thanks for the rep

[LinaVa]

Sorry, I am still confused...

My drawings revisions are listed in K2:U2. Revisions are P01,P02,...,P10,C01,C02,...,C10 (that is from oldest to newest).
Not all drawings have all listed revisions, some have just three revisions, some were revised 10 times.
In Cell H I have to select the latest revision of all listed in K2:U2.

it is something like max if "P01"<"P02"<"P03"<"P04"<"P05"<"C01"<"C02"<"C03"<"C04"<"C05"

please help

[TMS]

H2:

=MAX(IF($K$2:$U$2<>"",IF($K$2:$U$2<>"...",--RIGHT($K$2:$U$2,LEN($K$2:$U$2)-1))))

Array Entered with Ctrl-Shift-Enter

See the example

Regards, TMS

[LinaVa]

It says #VALUE..

[TMS]

Array Entered with Ctrl-Shift-Enter

???

Suggest you post a sample workbook indicating what you' ex got and what you need/expect

[LinaVa]

I should have started with attachment in the beginning. sorry.

[LinaVa]

In Column 'Latest Revision' I need to select the latest revision out of listed between columns K and U
assuming that P01-P10 are older than C01-C10 and 01 is older than 10

[TMS]

You don't make life easy for yourself or others, do you?

The columns don't match your description but hey, look at the example and see if that's what you want.

Regards, TMS

[LinaVa]

Thank You very Much! You saved me from a very boring task. It is exactly what I needed for now. There will be more drawings coming
in and more revisions will be listed. Next idea will probably be recognizing P revisions and C. As it can be P03 and C03 in the same row. And
C03 will be newer than P03.

Oh and sorry for making it harder. I deleted (instead of hiding) some columns before attaching file (#blond)..

Regards, Lina

[TMS]

Hello blond lady

See the attached updated workbook. I've added a couple of columns which separate out the P and the C values. Basically, the same as before but with an extra check. And then I've added a comparison. See if you like ...

Regards, TMS

[LinaVa]

Wow...

I must admit, first it looked like bunch of swear words.. lol

This is perfect. Thank You very much. How do you learn all this??

Regards,
Lina

[TMS]

How do you learn all this??

Well, I've spent quite a bit of time on it, maybe 18+ years. Using Excel, reading books about Excel, asking question about Excel, answering questions about Excel, and seeing the answers that others have provided. And, as time goes on, if I'm not sure how to do something, I know how to phrase the question in Google. The Internet is a wonderful thing

Regards, TMS

[LinaVa]

The Internet is a wonderful thing

agreed to that thanks for your help.