Need VBA expert to convert my Excel files

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 recuiter
Member since Jul 27, 2017
Hemlata G.
from Delhi, India

---

---

Looking for help? Checkout our video tutorial
How to search and apply for jobs

How to apply? Do you have more questions about the Job?
See frequently asked questions