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>

 

[mgirvin]

There is probably a better way, but maybe: =REPLACE(LOOKUP(2^15,1/SEARCH(B2,A2:A19),A2:A19),4,1,RIGHT(LOOKUP(2^15,1/SEARCH(B2,A2:A19),A2:A19),1)+1)

 

[mgirvin]

The last formula did not handle the 009 situation. But I think this one will: =LEFT(LOOKUP(2^15,1/SEARCH(B2,A2:A21),A2:A21),1)&TEXT(RIGHT(LOOKUP(2^15,1/SEARCH(B2,A2:A21),A2:A21),3)+1,"000")

 

[mgirvin]

Existing Codes	Select Letter	Current Last
A001	F	F009	=LOOKUP(2^15,1/SEARCH(B2,A2:A21),A2:A21)
A002		Next Avaliable
A003		F010	=LEFT(C2,1)&TEXT(RIGHT(C2,3)+1,"000")
B001		or
B002		F010	=LEFT(LOOKUP(2^15,1/SEARCH(B2,A2:A21),A2:A21),1)&TEXT(RIGHT(LOOKUP(2^15,1/SEARCH(B2,A2:A21),A2:A21),3)+1,"000")
B003
B004
C001
D002
A004
A005
A006
A007
A008
A009
A010
A011
E010
F009
A024

<tbody>
</tbody>

 

[circledchicken]

Hi Deep,

Assuming always a single letter followed by 3 digits, another alternative to Mike's formula might be something like this (you will need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula):

=B2 &
 TEXT(
   MAX(
     IF(
       LEFT(A2:A21) = B2,
       RIGHT(A2:A21, 3) + 1)),
   "000")

This might be slower than Mike's setup, but it should work on unsorted data as well (although it doesn't look like you need that).
If you wanted to accommodate different numbers of digits after the letter in either version, you could replace the RIGHT() part with a MID() alternative.

 

[mgirvin]

I knew there had to be a better way! Awesome formula, circledchicken!

 

[Deep Dave]

Thats awesome gyuz... All the Formulas worked as required. In the meantime I came up with a CSE Formula as I am trying to learn Array Formulas. I dont know how efficient the formula is, but it works as required too -

=IF(LEN(MAX(IF(B2=LEFT(Table1,1),RIGHT(Table1,3))+0)+1)=1,CONCATENATE(B2,"00",MAX(IF(B2=LEFT(Table1,1),RIGHT(Table1,3))+0)+1),IF(LEN(MAX(IF(B2=LEFT(Table1,1),RIGHT(Table1,3))+0)+1)=2,CONCATENATE(B2,"0",MAX(IF(B2=LEFT(Table1,1),RIGHT(Table1,3))+0)+1),IF(LEN(MAX(IF(B2=LEFT(Table1,1),RIGHT(Table1,3))+0)+1)=3,CONCATENATE(B2,MAX(IF(B2=LEFT(Table1,1),RIGHT(Table1,3))+0)+1),"")))

Ctrl+Shift+Enter


Thanx for the Help!!

Deep

 

[Deep Dave]

Hey I just found out something..

When I select a Letter from the Drop Down for Example "I" which currently has no Customers assigned to it, Formula given by Mike & CSE Formula written by me give incorrect results.

If I select "I", the ideal result should be I000. Mine returns I001.

However, the Formula given by circledchicken worked as required.

 

[circledchicken]

I knew there had to be a better way! Awesome formula, circledchicken!

Thanks Mike!

Thats awesome gyuz... All the Formulas worked as required.

Great, thanks for the feedback!

 

[mgirvin]

I think if you change circledchicken's outstanding formula from:

=B2&TEXT(MAX(IF(LEFT(A2:A23)=B2,RIGHT(A2:A23, 3)+1)),"000")

to:

=B2&TEXT(MAX(IF(LEFT(A2:A23)=B2,RIGHT(A2:A23, 3)+1,1)),"000")

it should work.



Here is the sheet I used:

ExistingCodes	SelectLetter	NextAvaliable
A001	P	P001	=B2&TEXT(MAX(IF(LEFT(A2:A23)=B2,RIGHT(A2:A23, 3)+1,1)),"000")	New
A002		P000	=B2&TEXT(MAX(IF(LEFT(A2:A23)=B2,RIGHT(A2:A23, 3)+1)),"000")	Old
A003
B001
B002
B003
B004
C001
D002
A004
A005
K000
A007
A008
A009
A010
A011
E010
F009
A029
A015
Q122

<tbody>
</tbody>

Do you concur, circledchicken?