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)

[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Existing Codes[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[/TR]
[TR]
[TD]A002[/TD]
[/TR]
[TR]
[TD]A003[/TD]
[/TR]
[TR]
[TD]B001[/TD]
[/TR]
[TR]
[TD]B002[/TD]
[/TR]
[TR]
[TD]B003[/TD]
[/TR]
[TR]
[TD]B004[/TD]
[/TR]
[TR]
[TD]C001[/TD]
[/TR]
[TR]
[TD]D002[/TD]
[/TR]
[TR]
[TD]A004[/TD]
[/TR]
[TR]
[TD]A005[/TD]
[/TR]
[TR]
[TD]A006[/TD]
[/TR]
[TR]
[TD]A007[/TD]
[/TR]
[TR]
[TD]A008[/TD]
[/TR]
[TR]
[TD]A009[/TD]
[/TR]
[TR]
[TD]A010[/TD]
[/TR]
[TR]
[TD]A011[/TD]
[/TR]
[TR]
[TD]A024[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Thank You,[/TD]
[/TR]
[TR]
[TD]Deep[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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