Next Number

[Pestomania]

Hi,

I have the following data (to start with) and am trying to create a "unique sku" for each category

Category	Category 2	Sku Start
Key Chain	State Shaped	KC0000
Coaster	State Puzzle	C00000

I would like to take the first letter of each word in the "Category" column and that is the first identifier in SKU Start.

And then I am hoping to get it to look for any SKU that already exists (in this case KC0000) and pick the next one which would be KC0001.

The SKU should always be 6 digits.

So a possible table would be:

Category	Category 2	Sku Start
Key Chain	State Shaped	KC0000
Coaster	State Puzzle	C00000
Key Chain	Funny	KC0001
Key Chain	Tarot	KC0002

Thank you!

 

[Peter_SSs]

See if this is what you want.

24 11 09.xlsm
	A		C
1	Category		Sku Start
2	Key Chain		KC0000
3	Coaster		C00000
4	Key Chain		KC0001
5	Key Chain		KC0002
6	Key Chain		KC0003
7	Stick		S00000
8	Coaster		C00001
9	Stick		S00001
10	Stick		S00002
11	Key Chain		KC0004
SKU

Cell Formulas
Range	Formula
C2:C11	C2	=LET(r,C$1:C1,pref,CONCAT(LEFT(TEXTSPLIT(A2," "),1)),x,LEN(pref),prev,FILTER(r,(LEFT(r,x)=pref)*ISNUMBER(-MID(r,x+1,1))*(r<>C$1),pref&"-1"),pref&TEXT(MID(TAKE(prev,-1),x+1,5)+1,REPT(0,6-x)))