Search highest value code in list

DroZ3

New Member
Joined
Mar 22, 2018
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Thanks in advance for your solutions to my problem.

I have a long list of codes which looks like this :
E001
E002
E003
Q001
RW01
RW02
E004
RW03
Q002
RW04

<tbody>
</tbody>
















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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
All formulas, but with helper columns:


Book1
ABCDEF
1E001E0014E004E004
2E002E0024E004
3E003E0034E004
4Q001Q0012Q002Q002
5RW01RW014RW04RW04
6RW02RW024RW04
7E004E0044E004
8RW03RW034RW04
9Q002Q0022Q002
10RW04RW044RW04
11EE01EE012EE02EE02
12EE02EE022EE02
Sheet9
Cell Formulas
RangeFormula
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.
 
Upvote 0
Maybe:


Book1
ABCDE
1CodesCodeMax
2E001E4E004
3E002Q2Q002
4E003RW4RW04
5Q001EE6EE06
6RW01
7RW02
8E004
9RW03
10Q002
11RW04
12EE05
13EE06
14
15
Sheet10
Cell Formulas
RangeFormula
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)))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top