Need a MACRO to automate Parent SKU creation for groups of children SKUs

[Michaelsgoldman]

Hello!

I need to automate a process but I do not know how to write macro script. I have included a color coded template file for easy reference. The Top three rows are header columns. Coloumn A (starting at row 4) would normally contain a long list of SKUs. These SKUs are for designs and each design has a unique set of letters used to describe it, with a four letter minimum. However each design also has color and size variations with the appropriate information tagged onto the end of the SKU. For example:

DESIGN 1:
ABCD_BLUE_S
ABCD_BLUE_M
ABCD_BLUE_L
ABCD_BLUE_XL
ABCD_BLUE_2XL
ABCD_BLUE_3XL
ABCD_GREEN_S
ABCD_GREEN_M
ABCD_GREEN_L
Etc…..

But they can also have more than 4 Letter identifiers…..

DESIGN 2:

A_ABCC_ABCD_BLUE_S
A_ABCC_ABCD_BLUE_M
Etc….

I need to know if there is a script that can search Column A (starting at row 4) and when it finds a group of SKUs that share the same beginning BUT CAN HAVE DIFFERENT COLOR AND SIZE INDICATORS tagged onto the end, it inserts a row above the first SKU and then copies only the information from that row INTO the columns marked in RED in the reference file. In addition, the information in the SKU column would be changed so that everything, Starting from the right, would be deleted up to and including the first underscore. (to get rid of the size indicator)

Basically, I have a long list of children SKUs and I need to make Parent SKUs for them. If I were to do this manually I would scan the list and…..as per the example above….insert a Parent SKU of ABCD_BLUE for design 1 and a Parent SKU of A_ABCC_ABCD_BLUE for Design 2. (**Please note: ABCD_BLUE would still be the Parent SKU for ABCD_GREEN and….they don’t all have 6 sizes…sometimes less.**) I would then copy the appropriate information into the parent SKU cells that I needed.

I have absolutely no idea how to write a macro for this and I realize that it is quite confusing to explain, but if anyone can help me I would be so very very grateful. If you have ANY suggestions please let me know. Thank you for your help in advance!

Color Coded Template Reference.xlsx

[Richard Buttrey]

Hi,

Can I suggest that you add some real data to your workbook and in a separate sheet manually add the results that you expect to get. It's always easier to offer advice if we can SEE what you want rather than have to interpret a narrative which often leads to misunderstandings of intent.

[Michaelsgoldman]

Hi Richard,

Thank you for the suggestion! I cannot put real data as it is confidential but I can put example data in its place. You are very right though as even in explaining it I left a few parts out. In Row BR of the attachment file I forgot to say that the value "child" changes to "parent" for the new line inserted. Also, in addition to the SKU being cut short to remove everything up to and including the first underscore from the right, the value in column B (Title column) has to be shortened as well. Everything up to and including the second "space" needs to be removed for the new parent row inserted.

I have added a new attachment. Sorry for the any confusion in my initial long description.

Excel Forum Template Reference.xlsx