Highest alphanumeric

[B. Liggett]

Is there a function/formula that will return the highest sort value from a
list of randomly ordered alphanumeric data? Example: (single column data)

Alpha09-199
Alpha10-001
Alpha10-037
Alpha10-002

"Alpha10-037" would sort to the highest value if sorted. I want a function
or formula that will return that value without actually sorting. Both MAX()
and MAXA() return 0. I would prefer not to split the column. Anyone have
any ideas? Thanks

 

[T. Valko]

Try this:

=LOOKUP(2,1/(COUNTIF(A1:A4,">"&A1:A4)=0),A1:A4)

 

[pshepard]

Hi B. Liggett,

Assuming your data is in range A1:A4:

=LOOKUP("zz",A1:A4,A1:A4)

Hope this helps

 

[T. Valko]

=LOOKUP("zz",A1:A4,A1:A4)

That will only work (reliably) if the data is sorted in ascending order.

You don't need to repeat the range:

=LOOKUP("zz",A1:A4)

 

[B. Liggett]

Try this:

=LOOKUP(2,1/(COUNTIF(A1:A4,">"&A1:A4)=0),A1:A4)

 

[B. Liggett]

Thanks Biff,
With some minor modifications, that worked great.

B. Liggett

 

[T. Valko]

You're welcome. Thanks for the feedback!