Generate SKU numbers for product list in Excel

[D1XII]

Hi Guys,

Total Newbie in Excel - but I can figure some things out.

I have a list of product categories structured like this:

Seasonal/Christmas/Trees/Ornaments/Red

I would like excel to generate numeric values for each category and a random value for each individual product. So

Seasonal = 4
Christmas = 52
Trees = 80
Ornaments= 01
Red = 7

Thus; the SKU = 45280017269, where 269 is the randomly generated product value.

I know there is a way and I'm sure it's easier than I think. Can any of you excel geniuses help me?

Thanks!

 

[BITechMacro]

=RANDBETWEEN(bottomvalue,topvalue)

 

[D1XII]

=RANDBETWEEN(bottomvalue,topvalue)

I don't think you read anything I wrote....

 

[D1XII]

Hi Guys,

Total Newbie in Excel - but I can figure some things out.

I have a list of product categories structured like this:

Seasonal/Christmas/Trees/Ornaments/Red

I would like excel to generate numeric values for each category and a random value for each individual product. So

Seasonal = 4
Christmas = 52
Trees = 80
Ornaments= 01
Red = 7

Thus; the SKU = 45280017269, where 269 is the randomly generated product value.

I know there is a way and I'm sure it's easier than I think. Can any of you excel geniuses help me?

Thanks!

Maybe I am unclear, apologies:

Column A Column B Column C Column D
[This is where my categories are. in this format] I'd like to convert A to this: I'd like to generate a random value I'd like to put column B,C,D together
Seasonal/Christmas/Trees/Ornaments/Red 4-52-80-01-7 269 4-52-80-01-7-269


Thanks!

 

[D1XII]

Here is an image, typing it out in the thread didn't work: https://postimg.org/image/tdfjyow5f/

 

[iliace]

Actually BITechMacro is onto something here.

To put the values together, you use the & operator. For example, if you want to get the values of A1 and B1 together, you would use this formula: =A1 & B1

From there, to get the value of each category, you may want to use a VLOOKUP against tables for each of the categories reflected in the SKU. In other words, do you already have a table for HolidayType (e.g. "Seasonal"), HolidayName (e.g. "Christmas"), ItemPurpose (e.g. "Tree"), ItemType (e.g. "Ornaments"), ItemColor (e.g. "Red")? Based on the image that you posted, you may need some way to split up the slash-separated list into individual components.

Now, when you say "random number", that's where it gets trickier. The approach suggested using RANDBETWEEN() will get you a random number, but since this is an SKU, you probably want a unique random number? If so, you need some way to know what random numbers have already been generated, to avoid repetition.

 

[BITechMacro]

Here is an image, typing it out in the thread didn't work: https://postimg.org/image/tdfjyow5f/

Incase you dont have any Table Lists for your Holiday,Item and color etc, here is a formula based sheet.

 

[D1XII]

Actually BITechMacro is onto something here.

To put the values together, you use the & operator. For example, if you want to get the values of A1 and B1 together, you would use this formula: =A1 & B1

From there, to get the value of each category, you may want to use a VLOOKUP against tables for each of the categories reflected in the SKU. In other words, do you already have a table for HolidayType (e.g. "Seasonal"), HolidayName (e.g. "Christmas"), ItemPurpose (e.g. "Tree"), ItemType (e.g. "Ornaments"), ItemColor (e.g. "Red")? Based on the image that you posted, you may need some way to split up the slash-separated list into individual components.

Now, when you say "random number", that's where it gets trickier. The approach suggested using RANDBETWEEN() will get you a random number, but since this is an SKU, you probably want a unique random number? If so, you need some way to know what random numbers have already been generated, to avoid repetition.

Yes, you are correct. I realized it was my lack of explanation - BITechMacro's reply was valid. My mistake.

I found a SKU generator in Excel and I modified it a bit. The only bit I'm stuck on is changing the final Created SKU to numeric values. I have attached the link to the file and also uploaded a screenshot. I think we're close!!! Thanks for your help!

Link to file: https://www.dropbox.com/s/7zk3mx4n5thqfsj/sku generator modified.xlsx?dl=0
Link to image: https://postimg.org/image/wv09o7og3/

 

[D1XII]

You're a genius!! Thank you!!

Now, the only issue is that if I wanted to quickly add more categories and products (like copy and paste a lot of them) it doesn't assign new values to those categories.

I'm not married to the numbers I entered earlier. Is there a way to generate values based off column A?

 

[D1XII]

Yes, you are correct. I realized it was my lack of explanation - BITechMacro's reply was valid. My mistake.

I found a SKU generator in Excel and I modified it a bit. The only bit I'm stuck on is changing the final Created SKU to numeric values. I have attached the link to the file and also uploaded a screenshot. I think we're close!!! Thanks for your help!

Link to file: https://www.dropbox.com/s/7zk3mx4n5thqfsj/sku generator modified.xlsx?dl=0
Link to image: https://postimg.org/image/wv09o7og3/

Sorry - here's a better image with the example values: https://postimg.org/image/jyxbctzb7/