Search highest value code in list

[DroZ3]

Thanks in advance for your solutions to my problem.

I have a long list of codes which looks like this :
[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]E001[/TD]
[/TR]
[TR]
[TD]E002[/TD]
[/TR]
[TR]
[TD]E003[/TD]
[/TR]
[TR]
[TD]Q001[/TD]
[/TR]
[TR]
[TD]RW01[/TD]
[/TR]
[TR]
[TD]RW02[/TD]
[/TR]
[TR]
[TD]E004[/TD]
[/TR]
[TR]
[TD]RW03[/TD]
[/TR]
[TR]
[TD]Q002[/TD]
[/TR]
[TR]
[TD]RW04[/TD]
[/TR]
</tbody>[/TABLE]
















Where each code is the same length (4 digits).
Where each code is unique.
Where each code class starts by one letter (see E, Q codes) or 2 letters (see RW code) and finishes by numbers.
Where each code only includes letters (A to Z) and numbers (0 to 9).
Where the number at the end of each code class will be in numerical order in the list (small to big) compared to the previous one, as in list above.
List of code is in the same column.
List of codes can be 50,000 codes long max (A:1 to A:50000).
No gaps in the list.

Considering the above I need a formula (no VB, no PT) to find the biggest code class, above would be : RW04, Q002, E004.

Thanks

 

[kweaver]

All formulas, but with helper columns:

Book1
	A	B	C	D	E	F
1	E001	E	001	4	E004	E004
2	E002	E	002	4	E004
3	E003	E	003	4	E004
4	Q001	Q	001	2	Q002	Q002
5	RW01	RW	01	4	RW04	RW04
6	RW02	RW	02	4	RW04
7	E004	E	004	4	E004
8	RW03	RW	03	4	RW04
9	Q002	Q	002	2	Q002
10	RW04	RW	04	4	RW04
11	EE01	EE	01	2	EE02	EE02
12	EE02	EE	02	2	EE02
Sheet9

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(0+MID(A1,2,1)),LEFT(A1,1),LEFT(A1,2))
C1	=IF(ISNUMBER(0+MID(A1,2,1)),RIGHT(A1,3),RIGHT(A1,2))
E1	=B1&REPT("0",LEN(C1)-1)&D1
F1	=IF(1=COUNTIF(E$1:E1,E1),E1,"")
D1	{=MAX((B1=$B$1:$B$12)*$C$1:$C$12)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E
1	Codes		Code	Max
2	E001		E	4	E004
3	E002		Q	2	Q002
4	E003		RW	4	RW04
5	Q001		EE	6	EE06
6	RW01
7	RW02
8	E004
9	RW03
10	Q002
11	RW04
12	EE05
13	EE06
14
15
Sheet10

Cell Formulas
Range	Formula
D2	=AGGREGATE(14,6,RIGHT($A$2:$A$15,4-LEN(C2))/(LEFT($A$2:$A$15,LEN(C2))=C2),1)
E2	=C2&TEXT(AGGREGATE(14,6,RIGHT($A$2:$A$15,4-LEN(C2))/(LEFT($A$2:$A$15,LEN(C2))=C2),1),REPT("0",4-LEN(C2)))

 

[DroZ3]

Thanks guys