Excel help generating product codes

-1

Good Day,

I need help generating different product codes for a range of values.

I want the product codes to be:

SKU0000 to SKU9999

and I also need the subproducts for each of the main products such as:

SKU0000-a to SKU0000-z

SKU0001-a to SKU0001-z

SKU0002-a to SKU0002-z

ect...

Can anyone advise me how to do this quickly?

Every product code has to be repeated 26 times with each character of the alphabet.

I have tried concatenation and then filling the series down, but the excel file grows in size rather quickly and has a tendency to crash quickly, often and without warning.

These values will be used in a data validation list, but if their is an alternative method to select them from a 'list' and store them, I am all ears.

Thanks for the help.

OmagaIII

Posted 2016-03-17T18:10:38.490

Reputation: 1

crash - how many are there? – Raystafarian – 2016-03-17T18:14:57.367

Well, full on it would be 260,000 codes. – EBGreen – 2016-03-17T18:26:59.873

Nevermind, found myself an alternative method that will work much better and faster. – OmagaIII – 2016-03-17T18:43:36.390

Please share your solution – Raystafarian – 2016-03-17T18:55:51.083

If you found a solution, please post it as an answer so others will know the solution. It is acceptable to answer your own question here. – CharlieRB – 2016-03-17T18:56:02.107

Answers

0

In A1 enter:

="SKU" & TEXT(ROUNDUP(ROW()/26,0)-1,"0000") & "-" & CHAR(96+MOD(ROW()-1,26)+1)

and copy down:

enter image description here

Gary's Student

Posted 2016-03-17T18:10:38.490

Reputation: 15 540

As you still need to automate this answer you could enter your formula in a cell A1, then press F5 (Go To) and enter the range address a1:a260000 then on the home tab click Fill select down and the formula will fill the range. – Antony – 2016-03-21T12:38:17.270

Asked: 2016-03-17T18:10:38.490

Viewed: 508 times

Active: 2017-04-08T10:34:55.820