Convert Flat Excel Worsheet into a specific format
Need to convert excel worksheet with product table into a specific format contains product attribute and variants. The output file format can be in Excel or CSV.
Product attributes: Size, Color1, Color2
Product Size variant: X, M, L, XL, ..etc
Product Size colors: Blue, Black, Red, etc
Every product has a unique code containing the type, size, and colors.
Product size in encoded with 1 digit of code.
Product colors in encoded with 1 or 2 digits of code.
Sheet1 = original workseet
Sheet2 = target worksheet
First, sort the Excel worksheet by Column D ascending.
Explanation on the target worksheet:
Col A:
sequence containing string "product1", "product2", etc
Col B:
value is taken from Sheet1 column D. If for the first row that the values are the same accross rows.
Col C:
value is taken from Sheet1 col S
Col D:
static string "product"
Col D:
value taken from Sheet1 col G
Col F:
value taken from Sheet1 col I
Col G:
value taken from Sheet1 col L
Col H:
value taken from Sheet1 col M
Col I:
value taken from Sheet1 col N
Col J:
value taken from Sheet1 col O
Col K:
value taken from Sheet1 col C up to the next different product at col B
Col L:
concatenate of "size" + substring(13,1) of column B + ",color" + substring(14,2) of column B
Col M:
value taken from Sheet1 col E
Col N:
value taken from Sheet1 col F
Col O:
new row with static string "attribute_size"
new row with static string "attribute_color"
or "attribute_color2" when the color code is 2 digits eg. substring(14,2) of column B is 2 digits length
Col P:
at row "attribute_size" : concatenate all "size*" at Col L
at row "attribute_color" or "attribute_color2": concatenate all "color*" at Col L
About the recuiterMember since Jul 27, 2017 Hemlata G.
from Delhi, India