Find the MAX of a TEXT/STRING in a cloumn

[Mojave]

Greetings all!

I need to find the latest week that something was submitted to in my table. The weeks are a combination of Year and week number, as such:

13 WK51
13 WK51
14 WK01
14 WK02
13 WK52

In the above sample, the correct answer is "14 WK02".

I do not want to add another helper column, the data is someone else's and must go back to them unharmed.

The data is not 'distinct', there are many duplicates.

Also, this is in a table/list object. It's field is identified as " Data_Table[Week_Num] "

Of course, =MAX doesn't work. But what would? I have searched but haven't found a solution. I imagine it's some kind of array function, but I'm pretty fuzzy on those.

Thank you for considering my problem!

 

[NARAYANK991]

Hi ,

If your data has a standard format , the following formula may help :

=SUBSTITUTE(MAX(LEFT(Data_Table[Week_Num],3)+RIGHT(Data_Table[Week_Num],2)*0.01),"."," WK")

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[Mojave]

Narayan,

Wow! Thank you! That is nearly perfect!

The only issue is that for a week like "14 WK40", the answer comes back as "14 WK4", the ending zero is left off.

I'll also have to research the Substitute function, don't kow what it's doing.

Thank you again very much.

 

[NARAYANK991]

Hi ,

Sorry , I didn't consider this situation. Try this :

=SUBSTITUTE(TEXT(MAX(LEFT(Data_Table[Week_Num],3)+RIGHT(Data_Table[Week_Num],2)*0.01),"00.00"),"."," WK")

again entered as an array formula , using CTRL SHIFT ENTER.

Narayan

 

[Mojave]

Perfect! Thank you!

I nominate you for Excel Citizen of the day! I don't know if there is such a thing, but you should win it.

 

[Haseeb A]

Hello Mojave,

If your data s always as you posted this Array Formula should work.

=INDEX(A1:A50,MATCH(0,COUNTIF(A1:A50,">"&A1:A50),0))

 

[Mojave]

Haseeb, thank you also! That solution works perfectly.

Incidentally, both of these formulas can be reversed to find the MIN by either replacing MAX with MIN, or changing the ">" Greater Than to "<" Less Than.

Fine work, thank you people.