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!

 

[iliace]

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?

As I mentioned earlier, my recommendation would be to create a separate list (probably on a separate sheet) of the different values available in each category. Then, use VLOOKUP to pull up the corresponding number for each item.

 

[ChrisGT7]

I created this SKU generator: https://s22.postimg.org/ar6mytyw1/SKU.jpg

In B2, use this formula: =VLOOKUP(C2,$I$3:$J$44,2,FALSE)&"-"&VLOOKUP(D2,$K$3:$L$44,2,FALSE)&"-"&VLOOKUP(E2,$M$3:$N$44,2,FALSE)&"-"&VLOOKUP(F2,$O$3:$P$44,2,FALSE)&"-"&VLOOKUP(G2,$Q$3:$R$44,2,FALSE)&"-"&INDIRECT("H"&ROW()+1)

Copy it down to the rest of cells of column B to create the unique SKU of each product.

The second table (columns I:R) is your category database which column B uses it to make the preferable SKU according to your data entry (categories and colours).

I hope I could help a little more.

EDIT: Another suggestion is, if you would like to avoid column H, replace "INDIRECT("H"&ROW()+1)" with this: "TEXT(ROW();"0000")". Using the Number of row is also a unique digit.

 

[D1XII]

I created this SKU generator: https://s22.postimg.org/ar6mytyw1/SKU.jpg

In B2, use this formula: =VLOOKUP(C2,$I$3:$J$44,2,FALSE)&"-"&VLOOKUP(D2,$K$3:$L$44,2,FALSE)&"-"&VLOOKUP(E2,$M$3:$N$44,2,FALSE)&"-"&VLOOKUP(F2,$O$3:$P$44,2,FALSE)&"-"&VLOOKUP(G2,$Q$3:$R$44,2,FALSE)&"-"&INDIRECT("H"&ROW()+1)

Copy it down to the rest of cells of column B to create the unique SKU of each product.

The second table (columns I:R) is your category database which column B uses it to make the preferable SKU according to your data entry (categories and colours).

I hope I could help a little more.

EDIT: Another suggestion is, if you would like to avoid column H, replace "INDIRECT("H"&ROW()+1)" with this: "TEXT(ROW();"0000")". Using the Number of row is also a unique digit.

This is brilliant, ChrisGT7.

I will try to replicate now. I got kind of close, but I think your way is much more fluid. Will report back - been up since yesterday trying to figure this out. I'm a designer - not a coder . Thanks, again!

 

[Mark C.]

Hi BITechMacro:

I am new and would like to know how you show the post you are replying to?

 

[ChrisGT7]

Hi BITechMacro:

I am new and would like to know how you show the post you are replying to?

Mark, at the right corner of each post there is a button "Reply With Quote". Use it to quote it.

 

[Mark C.]

Mark, at the right corner of each post there is a button "Reply With Quote". Use it to quote it.

Awesome thank you feel stupid now.