I have a project where I need to transform a long narrow data range into a matrix, adding blank rows and repeated headers to separate each grouping within the matrix.
For some behind the scenes information, the long narrow data is retrieved from 100+ separate workbook (each workbook representing a 'Style' in the attached file), each containing a cut list and Bill of Materials for different manufactured products. I have achieved this through VBA loops. This phase produces the desired results. Now there is a need to transform this list of all the SKUs into a matrix pricelist format. (By the way, I did not use Power BI because I am not well versed in BI, and also the 100+ workbooks are not structured in the most professional manner). I plan to continue using this VBA process to create the long/narrow list, but I need a user friendly method of transforming this to a matrix.
I have attached a sample workbook which shows the desired results, along with some explanatory notes. On Sheet2 of the attached file I have a PivotTable which seems to fall short of providing what I need. I left it there, just in case someone responds with "Build a PT". I don't believe it will work quite that easily.
I suspect that this could be achieved with Power BI, but I am not sure how to go about doing so. I have worked Power BI, and am familiar with many of its features, but I am struggling with what I should ask Power BI to do for me. I am not against VBA, if someone can get me started and show me that it is the preferred method.
Thank for your responses.
Bookmarks