I received numerous spreadsheets of data (each containing thousands of products) with the product attributes that title each column of values for a broad spectrum of products. I need a macro to transform the format for a website.
If you can only assist in helping to transpose data to sheet2, that would be greatly appreciated.
I would like to preserve the row associated with the product ID but transpose the attribute title (title preceded by <b> followed by “:</b>”- without quote marks) to precede the value (VAL). Because the spreadsheet is a consolidation of many different product types, there are many empty fields because values may not exist for a specific SKU.
These attributes are interspersed with columns where I would prefer to keep the structure intact (not transpose title). Prior to running the macro, I can highlight columns that I do not want translated but would accept any suggestion. The non-translated columns would appear after the last ATT/VAL set and should remain a uniform column, meaning the column location does not shift by SKU (row). The raw data would be provided on Sheet1 and the transposed data (new structure) would occur on Sheet2, where my example is shown.
As the last step which I would prefer to manually activate as a separate routine on Sheet 2, is to have HTML tags applied to the translated attributes. I have listed the code, noting the repeating steps and provided sample code. These are shown on Sheet 2 between columns BW-CE
I have attached a sample sheet with raw data which I have transformed the first row to demonstrate (on Sheet 2) the desired output. As you can see, it is difficult to pre-determine the number of Att/Val combinations each SKU would yield. I created 30 ATT/VAL fields but don't know how many any product will populate.
Thank you in advance for your assistance.
Bookmarks