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]

Welcome to Mr Excel

To get the next available try this array formula
="PTF-"&1+MAX(IF((LEFT($B$6:$B$1001,3)="PTF"),--RIGHT($B$6:$B$1001,4)))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Rick Rothstein]

Welcome to Mr Excel

To get the next available try this array formula
="PTF-"&TEXT(1+MAX(IF((LEFT($B$6:$B$1001,3)="PTF"),--RIGHT($B$6:$B$1001,4))),"0000")
Ctrl+Shift+Enter

It looks to me like the OP would want a 4-digit number after the letters (hence, the red additions above). Also, I don't think you need the absolute references as this appears to be a formula that will only be placed in a single cell.

 

[Rick Rothstein]

This formula seems to also work...

=TEXT(1+IF(LEFT(B6:B1001,3)="PTF",RIGHT(B6:B1001,4)),"PTF0000")

 

[Marcelo Branco]

It looks to me like the OP would want a 4-digit number after the letters (hence, the red additions above).

You are right. I replied too fast - was thinking only in how to get the max

M.

 

[KBBersch]

Thanks for both replies. I would appreciate a discussion of WHY the you chose the particular formula so I can maybe not have to ask as many questions.

Thanks.

 

[Marcelo Branco]

This formula seems to also work...

=TEXT(1+IF(LEFT(B6:B1001,3)="PTF",RIGHT(B6:B1001,4)),"PTF0000")

I think the OP wants the max...

M.

 

[Rick Rothstein]

This formula seems to also work...

=TEXT(1+IF(LEFT(B6:B1001,3)="PTF",RIGHT(B6:B1001,4)),"PTF0000")

I think the OP wants the max...

Whoops! This is what I should have posted...

=TEXT(MAX(1+IF(LEFT(B6:B1001,3)="PTF",RIGHT(B6:B1001,4))),"PTF0000")

 

[Marcelo Branco]

Thanks for both replies. I would appreciate a discussion of WHY the you chose the particular formula so I can maybe not have to ask as many questions.

Thanks.

Try something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Code​

[/td][td][/td][td]

Prefix​

[/td][td]

Next Available​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

PTF-0001​

[/td][td][/td][td]

PDL​

[/td][td]

PDL-0005​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

PDL-0001​

[/td][td][/td][td]

PTF​

[/td][td]

PTF-0006​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

PTF-0002​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

PTF-0003​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

PTF-0004​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

PDL-0002​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

PDL-0003​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

PDL-0004​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

PTF-0005​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in E6 copied down
=TEXT(1+MAX(IF(LEFT(B$6:B$1001,3)=D6,--RIGHT(B$6:B$1001,4))),CHAR(34)&D6&CHAR(34)&"-0000")
Ctrl+Shift+Enter

M.

 

[Rick Rothstein]

Array formula in E6 copied down
=TEXT(1+MAX(IF(LEFT(B$6:B$1001,3)=D6,--RIGHT(B$6:B$1001,4))),CHAR(34)&D6&CHAR(34)&"-0000")
Ctrl+Shift+Enter

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")

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")