Max (Alphanumeric) + 1

[qadeerahmed]

Hi,

I am looking to find the Max Alphanumeric Serial Number that is in a column and increment by 1 by meeting multiple criteria provided.

here is what I found on this site
http://www.mrexcel.com/forum/excel-questions/749540-find-max-alphanumeric-serial-number.html

[{="GI-" &MAX([COLOR=blue]([COLOR=red]MID([COLOR=green]$C$1:$C5,3,255[/COLOR])&".00"[/COLOR])+0[/COLOR])+1}/CODE]


Here is Example
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Dept.[/TD]
[TD]Doc#[/TD]
[/TR]
[TR]
[TD]IG[/TD]
[TD]Purchasing[/TD]
[TD]IG-1123[/TD]
[/TR]
[TR]
[TD]IG[/TD]
[TD]Purchasing[/TD]
[TD]IG-1124[/TD]
[/TR]
[TR]
[TD]IG[/TD]
[TD]Purchasing[/TD]
[TD]IG-1122[/TD]
[/TR]
[TR]
[TD]IG[/TD]
[TD]Purchasing[/TD]
[TD]IG-1122-A[/TD]
[/TR]
[TR]
[TD]IG[/TD]
[TD]Purchasing[/TD]
[TD]IG-1125[/TD]
[/TR]
[TR]
[TD]CP[/TD]
[TD]Procurement[/TD]
[TD]CP-1210[/TD]
[/TR]
[TR]
[TD]CP[/TD]
[TD]Procurement[/TD]
[TD]IG-1211[/TD]
[/TR]
[TR]
[TD]CP[/TD]
[TD]Procurement[/TD]
[TD]IG-1211-A[/TD]
[/TR]
</tbody>[/TABLE]
e.g. 
[B]IF[/B]
Criteria= Purchasing [I](Dept.) [/I][B]then
[/B]Output[I]= [/I][I]Max(Doc#)+1[/I] i.e. [B][U]IG-1126[/U][/B]
[TABLE="width: 324"]
<tbody>[TR]
[TD]Appreciated![/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 243"]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]<colgroup><col width="324" style="width: 243pt; mso-width-source: userset; mso-width-alt: 11849;">[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]<tbody>[TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="width: 324, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]</tbody>[/TABLE]

 

[Canapone]

Hi,

first attempt using AGGREGATE (avalaible from Excel 2010)

="IG-"&AGGREGATE(14,6,MID($C$1:$C8,4,10)+1,1)

Hope it helps