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