Max in a list of alphabets/texts

[rarmn]

Hello,

I have a list in increasing order of value

A,B,C,...,Z,AA,AB,AC,...,AZ,BA,BB,...,BZ (so on)

I want to find the maximum in this list. max should be "BZ" and minimum should be "A" in the above list.

Any suggestions.

Thanks.

 

[rarmn]

Hey, Thanks for writing!!

You can use the following formula:

=INDEX(A1:A9,MATCH(MAX(CODE(A1:A9)+
IFERROR(CODE(MID(A1:A9,2,1)),0)),CODE(A1:A9)+IFERROR(CODE(MID(A1:A9,2,1)),0),0))

Please change the referencing as per the requirement, in my case the values were in A1:A9.
Make sure you press Control+Shift+Enter while entering the formula, since its an array operation.

Hope this will work.

Thanks/Raj

Thank you for the formula. But when i have "AZ" and "ZA" in the list if "AZ" occurs first the formula will pick "AZ" (ZA is > AZ) because the code value is 155 for both "AZ" and "ZA". Any suggestions. Thank you.

 

[rarmn]

In that case, try ..

rarmn

	A	B	C	D	E	F	G	H	I
2	A	D	E	V	AB	AC	AJ	AK
3
4	AK

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A4 =LOOKUP("ZZ",2:2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you for the formula. I have a list both sorted and unsorted. I made up that example to show the ascending order. This formula works great for me for the sorted list. Thank you once again.

 

[Peter_SSs]

I have a list both sorted and unsorted.

For unsorted try this array formula.
There may be a shorter way, but I couldn't see it!
This assumes 1 or 2 UPPER CASE letters and/or blank cells in the range.
Formula in S2 is copied down (after Ctrl+Shift+Enter confirmation)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1																			'Max':
2	A	D	E	V	AK	AA	BK	AJ	BS	AZ	B			B		X	BB		BS
3			AZ			ZA		ZA	AZ										ZA
4
5	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX		XX
6	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q		Q
rarmn unsorted

 

[rarmn]

For unsorted try this array formula.
There may be a shorter way, but I couldn't see it!
This assumes 1 or 2 UPPER CASE letters and/or blank cells in the range.
Formula in S2 is copied down (after Ctrl+Shift+Enter confirmation)

rarmn unsorted

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

'Max':

2

A

D

E

V

AK

AA

BK

AJ

BS

AZ

B

*

*

B

*

X

BB

*

BS

3

*

*

AZ

*

*

ZA

*

ZA

AZ

*

*

*

*

*

*

*

*

*

ZA

4

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

5

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

XX

*

XX

6

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

*

Q

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:49px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula S2 {=TRIM(CHAR(LEFT(MAX(CODE(RIGHT(" "&A2:Q2,1))+100*CODE(LEFT(RIGHT(" *"&A2:Q2,2),1))),2))&CHAR(RIGHT(MAX(CODE(RIGHT(" "&A2:Q2,1))+100*CODE(LEFT(RIGHT(" *"&A2:Q2,2),1))),2)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks a lot for your help. The formula works great. Thanks.

 

[Peter_SSs]

Thanks a lot for your help. The formula works great. Thanks.

Cheers, glad it worked for you.