+ Reply to Thread
Results 1 to 5 of 5

Transform data to matrix (with repeated headings)

  1. #1
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Transform data to matrix (with repeated headings)

    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.
    Attached Files Attached Files
    Last edited by pinebush; 01-13-2021 at 08:06 PM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Transform data to matrix (with repeated headings)

    Please try Power Query

    Please Login or Register  to view this content.
    where selectSKU is parameter for each SKU
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Re: Transform data to matrix (with repeated headings)

    Thank you for your immediate response, Bo-Ry. I'm not able to load this to the worksheet, due to this error "Expression.Error: The import selectSKU matches no exports. Did you miss a module reference?" Is this a syntax error, or did you provide me with only partial code?

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Transform data to matrix (with repeated headings)

    Check the file in Post#2

    Where selectSKU is parameter for each SKU
    or change selectSKU to "A"

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-10-2011
    Location
    Canada
    MS-Off Ver
    Excel 2019 / MS365
    Posts
    55

    Re: Transform data to matrix (with repeated headings)

    Thank you for your assistance. Your solution did not fully satisfy the needs of the actual dataset, but it was certainly enough to get me on the right track, and to give me an example of what is possible with Power Query. And to convince me that I need to become more familiar with Power BI.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Wizard transform Table to Matrix and rearrangement
    By great_AS in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 08-20-2018, 06:36 AM
  2. [SOLVED] Transfer data with Horizontal headings to sheet with vertical headings
    By caabdul in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-09-2018, 10:33 AM
  3. Replies: 4
    Last Post: 02-24-2014, 03:49 PM
  4. [SOLVED] extract data that is not repeated within this matrix
    By marreco in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-08-2012, 05:36 PM
  5. Transform correlation matrix into a vector-column
    By shpenev in forum Excel General
    Replies: 1
    Last Post: 10-19-2012, 05:20 PM
  6. [SOLVED] Append data to repeated values with a fixed array to be repeated on value change
    By anchuri_chaitanya in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-06-2011, 12:30 AM
  7. Transform matrix into a row of numbers
    By studentin_in_not in forum Excel General
    Replies: 1
    Last Post: 09-13-2010, 09:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1