SKU Automation

[holt0222]

Hello, I am new to using excel but I am trying to make it so that it will automatically create a SKU for me. I have items that range such as Books, Movies, TV Shows, and Collectibles. These are just some examples of the categorizes that I have. Currently I have the excel sheet to look something like this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Unit Price[/TD]
[TD]Stock[/TD]
[TD]UPC[/TD]
[TD]Type of Product[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to make it so that the first letter of type of product is sent to the beginning of the SKU and then numbers are added randomly to the end of the letter. If anyone knows how I would do this it would be greatly appreciated. I am needing to make it so that when I add a new item it automatically is assigned a SKU number.

 

[FDibbins]

the first letter of type of product is sent to the beginning of the SKU and then numbers are added randomly to the end of the letter

depending on how many numbers you add, if they are random, there chances are that, at some point, you will end up wit duplicate SKU's. Adding extra numbers will only postpone that, not eliminate it. It would be better if you had an actual sequence/logic to the SKU?

Also, if you have more than 26 different items, again, you risk having duplicate SKU's

Perhaps use the 1st 2 letters of/for each category, then great a series of increasing numbers for each category?

 

[holt0222]

What about if you used the first letter from the category and then just went up from 1. Would it be possible to do it in a sequential order?

 

[FDibbins]

yes, we could do that, but 2 letters would be better - what if you cat "Collectibles" and "Cards" and/or "Cars"?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

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

1​

[/td][td]Name[/td][td]Type[/td][td]Sku[/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]Books[/td][td]B1[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]Movies[/td][td]M1[/td][/tr]

[tr][td]

4​

[/td][td]dd[/td][td]TV Shows[/td][td]T1[/td][/tr]

[tr][td]

5​

[/td][td]cc[/td][td]Collectables[/td][td]C1[/td][/tr]

[tr][td]

6​

[/td][td]ee[/td][td]Books[/td][td]B2[/td][/tr]

[tr][td]

7​

[/td][td]rr[/td][td]Books[/td][td]B3[/td][/tr]
[/table]

C2=LEFT(B2,1)&COUNTIF($B$2:B2,B2) copied down

 

[holt0222]

Thank you so much. I have taken your advice and done the first 2 letters.

 

[FDibbins]

Happy to help