Find maximum number pith alpha prefix

[KBBersch]

I have a column that contains asset numbers in the form of PDL-xxxx and PTF-xxxx. I need to display the maximum number for each prefix, or better yet the next available number for each prefix. More entries will be added and the cells need to update as that happens. I tried =INDEX($B$6:$B$1001,MAXA(IF((LEFT($B$6:$B$1001)="PTF"),$B$6:$B$1001))) but that always returns the first entry in the column. I know I'm missing something but I'm not sure where to go from here. Any help is appreciated.

Thanks,
Kelly Bersch

 

[Marcelo Branco]

If you put the 1+ inside the MAX function call, then you can eliminate the double minus in front of the RIGHT function call as doing that would involve the RIGHT function in a mathematical operation the same as the double negative did...

=TEXT(MAX(1+IF(LEFT(B$6:B$1001,3)=D6,RIGHT(B$6:B$1001,4))),CHAR(34)&D6&CHAR(34)&"-0000")

Good idea

And then, if you want to save 9 characters, you can use proper quoting in place of the CHAR(34) calls...

=TEXT(MAX(1+IF(LEFT(B$6:B$1001,3)=D6,RIGHT(B$6:B$1001,4))),""""&D6&"""-0000")

Yes, i know this is possible, but every time i get confused with so many quotes, so I prefer CHR (34)

M.