1
I have an excel sheet that has several thousand products on it. Each product consists of three columns of data with variable number of rows.
This is how it currently looks like
This is what I need to make it into
Manufacturer changes every 10-50 products. I'm guessing the solution (if one exists) needs some sort of a loop that goes through the whole table and collects the data before repopulating it into correct fields.
Each product can have variable amount of features, but the names (or labels) of these features are known. How ever each product can also have variable amount of different prices and the names of these fields are not known. Thus both price label and price value needs to be added on the individual product row.
This goes so over my head I don't even know how to google for the right answer. Probably some sort of combination of macros and indexing? I'd be grateful if someone can even point me at the right direction.
Hurme
Posted 2019-09-18T12:37:49.017
Reputation: 11
2I'd recommend to create VBA procedure which converts source to destination. Iterate over rows. Detect (and remember in a variable) current manufacturer by columns 2 and 3 are empty, the same for current product by empty columns 1 and 3, and process property lines detected by non-empty column 3 using last manufacturer and product values. Because the amount of prices/features may vary - use collection or dynamic array for them. When new product detected or end of data reached then build and save each next line of destinational data. – Akina – 2019-09-18T12:47:23.340
1Alternatively you may try to convert this structure to 5-column structure (add according manufacturer-product-model values to each label-value pair), then create pivot table. – Akina – 2019-09-18T12:52:57.710