Next Customer Code

[Deep Dave]

I have a bunch of existing customer codes, in Range A1:A19. in Cell B2, I have a drop down with letters from A - Z. The user should select any letter from the Drop Down, and in cell C2, the next available Customer Code starting with that letter should be visible. In the below example, if the user selects B from the Drop Down, the output in cell C2 should be B005. Can anyone help me with this. (Request a Formula as an answer and not VBA)

Existing Codes A001 A002 A003 B001 B002 B003 B004 C001 D002 A004 A005 A006 A007 A008 A009 A010 A011 A024 <tbody> </tbody>

Thank You,

Deep

<tbody>
</tbody>

 

[Deep Dave]

Works like a charm.. Thnx Mike & circledchicken

 

[circledchicken]

Do you concur, circledchicken?

Yes sir, nice one.

 

[markmzz]

Another way (array formula - use Ctrl+Shift+Enter and not only Enter):

=B2&TEXT(MAX(IFERROR(1+SUBSTITUTE(A2:A18,B2,""),1)),"000")

Markmzz

 

[Deep Dave]

Hi markmzz -

The Formula works correctly except when a letter is chosen that does not already have a Code assigned to it.. For example If I select "I" from the Drop Down, it returns I001, but it actually should return I000.

Great formula none the less..

Thnx
Deep

 

[markmzz]

Hi markmzz -

The Formula works correctly except when a letter is chosen that does not already have a Code assigned to it.. For example If I select "I" from the Drop Down, it returns I001, but it actually should return I000.

Great formula none the less..

Thnx
Deep

If you want I000 in this case, try this small modification (array formula - use Ctrl+Shift+Enter and not only Enter):

=B2&TEXT(MAX(IFERROR(1+SUBSTITUTE(A2:A18,B2,""),[COLOR="#FF0000"][B]0[/B][/COLOR])),"000")

Markmzz

 

[circledchicken]

Hi Markmzz,

That's a nice alternative!

 

[markmzz]

Hi Markmzz,

That's a nice alternative!

Thanks Circledchicken!

Markmzz