MAX for text

I have a list of values in one column.
The values are each made up of two alpha and two numeric values.
I need to find the "maximum" of these values.

e.g.
TC06
TC08
TC01
TC09
TC08
TC09

needs to return "TC09".

Oh - I need to do this without adding any columns (I could do it that way).

Any ideas?
Thanks

With
A1:A10 containing text (perhaps some blanks, too)

This formula returns the "largest" text value:

Please Login or Register  to view this content.

Using your sample data, the formula would return: TC09

Does that help?

That looks like a simple formula, but it really is an advanced formula. Explaining it will be a challenge. Let's see how I do.

With
A1:A10 containing text (and possibly some blanks)

This formula returns the Maximum text value.

Please Login or Register  to view this content.

That maximum is the item that would appear at the bottom of the list
if you were to sort the list in ascending order.

The COUNTIF function can determine if one text value is greater than
another (using the ascending order logic I mentioned). One possible sticking
point would be blank cells. The formula would indicate that blanks are
larger than any other cells...and return a zero for each blank....the same
value that the "largest" text value will return. So...we append a tilde (~) to
every cell we test. The tilde is one of the "smallest" values you can type
with one key on the keyboard. Now,

This part of the formula: COUNTIF(A1:A10,">"&A1:A10&"~")
returns an array of 10 values (one for each cell we test). Each value
represents the number of cells in the range on the left of the formula
that are "larger" than the value in the test cell in the right of the formula.

Example:
A1: a
A2: b
A3: c
A4:A10 are blank

2 values are greater than "a"
1 value is greater than "b"
0 values are greater than "c"
For each blank cell, 3 values are greater than "~".

In that example, this formula: COUNTIF(A1:A10,">"&A1:A10&"~")
would return this array: {2;1;0;3;3;3;3;3;3;3}

The 0 in that array represents the cell that has no other cells
larger than it (A3 with "c").

This formula section: (COUNTIF(A1:A10,">"&A1:A10&"~")=0)
test if each value in that array equals 0 (zero).
The result is a series of TRUE/FALSE values which, when divided into 1,
are converted into 1's and 0's, respectively.

Still using our sample data, (COUNTIF(A1:A10,">"&A1:A10&"~")=0)
returns

Please Login or Register  to view this content.

and this: 1/(COUNTIF(A1:A10,">"&A1:A10&"~")=0)
is the equivalent of:

Please Login or Register  to view this content.

which calcs to:

Please Login or Register  to view this content.

Note: 1/0 returns an error.

As if that isn't confusing enough....here comes the tricky part!
1) The LOOKUP formula ignores error values.
2) If the LOOKUP formula is trying to find a value that is larger than
any other value in the list....it simply returns the LAST VALID ITEM in the list.

In our case, we will have a 1 in the midst of a buch of error values.
Consequently, that 1 is the only valid item in the lookup list!

So....

Please Login or Register  to view this content.

Tries to find the number 2 in the lookup list. Whatever value the
LOOKUP function determines is a match, that value's corresponding item
in the value list will be returned.

Since 2 is larger than any value in:

Please Login or Register  to view this content.

The LOOKUP function matches on 1 (the 3rd item in the list)
and returns the 3rd item in A1:A10....which is "c".

I hope that helps.

Thanks Ron

I'll give it a go.

Tony