Auto Generate Child SKU's based off a list of parent skus and a list of colors

Hello Everyone,

I am in dire need of some help with automating the creation of skus based off the parent sku and the color options. I have attached an example of a simplified version of what I need. Essentially I have a list of parent skus in the 20 to 40k mark, all of these skus respresent one design style I sell for decals. However I need to offer both color and size options for each which all require seperate skus. In the past I would use plugins with my website to fake this on the front end of the website. However now I am using shopify so I can push my products to many other market places but I can control them all from shopify. All of these market places and shopify require both parent and child skus. So what I need to be able to do is be able to load a list of parent skus and have excel auto generate variotion skus based off a list pf colors or material types. So I need the generator to basically concatenate the abrevation to end of the parent sku and post it in a different column. So example my first parent sku is FD-TEST-001. I have a list of colors which all have a corisponding abbreviations, I need it to run a loop through of all the color options taking the abrevation for the color and concatenating to the parent sku in a diffrent column and then move to the second parent sku and repeat untill the parent sku list is done. I would like to use a macro with a button called generate to excute the command.


PARENT SKUS:
FD-TEST-001
FD-TEST-002

COLORS:
BLACK GLOSS (-BLKG)
BLACK MATTE (-BLKM)

RESULTS:
FD-TEST-001-BLKG
FD-TEST-001-BLKM
FD-TEST-002-BLKG
FD-TEST-002-BLKM


Can somebody help point me in the right direction? I need to do this for size and color options in the future as well as many other minor automations, but I really really just need a way of auto generating skus based off the parent sku and color abbreviation for now that would help so much. I use to have my programmer automate this and many other functions for in the past but its been a year now that he is MIA so I need to find a way to do this my self from excel directly.

-Jason

Try in E2

=INDEX($A$2:$A$22,INT((ROWS($1:1)-1)/COUNTIF($C$2:$C$50,"?*")+1))&"-"&INDEX($C$2:$C$14,MOD(ROWS($1:1)-1,COUNTIF($C$2:$C$50,"?*"))+1)

The COUNTIF(....) counts the number of color options.

Thank you, John

That was immense help that was 40% of the battle easy. However I just realized, I definitely need to base this off two separate lists both size and color. If I needed it to also reference the second list in a separate column is there a way to tell it to run through both? I could make a long list like BLKG-SM06, BLKG-SM08 ect but I will be needing to use diffrent size and color parameteres depending on the types of designs im upoading so it would be great to be able to do it from two columns.

I am also curious can I apply this to a macro button or hotkey so that it runs the formula down the list for me? To create the current results I had to sit there and drag the formula down for thousands of rows as currently I will be offering close to 100 variations for each design and just this one test category has 629 parent SKUS which represent 629 different kanji designs. This amounts to test options that amount to over 6k rows in excel. I have edited the formula a bit as I changed the spread sheet a bit, added real skus and have made an example. Please see attachment and thank you again.

Thank you John!

So found a few more issues . I need to post the same data from a particular cell call it the source cell, and I want it to paste that same data in another cell but I need it to only post on the first row of a new variable sku. So for instance if I only need the product description cell to be filled on the first row of a new set of variable skus, how could I do that using the same vba as before. meaning instead of say skipping a preset number of cells, how can we write it so it knows to only paste the first time a new parent sku starts? I need this because otherwise it populates the description which is a lot of html into each description cell for each variable. Though the only thing that is changing is the decals color and size so no need to have a different product description. Also shopify then thinks its a new sku entirely and skips the child skus and considers them new parent skus. It does that with other fields as well that only need to be entered once along with new parent sku.

So if I need say the data from cell (GENERATE_SM_OUTDOOR!K2) to be pasted to cell ( SHOPIFY_RESULT_DATAFEED!C?) ? because depends on when it starts a new set of variables and I need it to follow the same dynamic counting from the VBA code above this way no mater if i add or change possible variable options this code will always paste the description into the first cell on the same row as a starting variable. So in this case SHOPIFY_RESULT_DATAFEED!C2 then skip 65 as there's 65 options per sku right now but if I change the materials or sizes I offer etc using a static skip number would be off. Any ideas even if I have to manually change this each time I adjust the template?

Current excel file I am working with is uploaded this will be ever changing and will be cleaned up eventually once its outputting 100% clean data feed information for shopify.