Converting a matrix data layout to a tabular layout

{ 74 Comments }

By Ashish Mathur · · , · Leave a comment

In this file, the source data sheet is a pasted special pivot table.  The task is to convert this pivot table data layout to the result shown in the “Desired result” sheet.  Effectively, we need to convert a matrix like data layout to a tabular layout.  We need to “denormalise” the pivot table.

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  You may refer to Power Query result worksheet.

You may watch a short video of my solution here

Solution for all versions of MS Excel

Please try the following steps:

1. Select A6:F234 on the Source Data worksheet
2. Press Ctrl+G > Special > Blanks > OK.  This process will select all blank cells in range A6:F234
3. With the selection in place, press the = key
4. Press the up arrow key
5. Press Ctrl+Enter.  You will now see all blanks cells filled up the values just above.
6. Select A6:F234, copy the range and paste them as values (Alt+E+S+V)

To denormaise the pivot table, the multiple columns appearing in the row labels area of the pivot table (range A6:F234) will need to be crunched into one column by concatenating.  The result is shown in G7:G35 of “Source data adjusted” sheet.  Likewise, the multiple rows appearing in the columns labels area of the pivot table (range G4:DE5) will need to be crunched into one row by concatenating.  The result is shown in H6:DF6 of “Source data adjusted” sheet.

The process of denormalising is as follows:

1. Select range G6:DF235 of the “Source data adjusted” sheet
2. Press Alt+D+P > Multiple Consolidation ranges > Next
3. Select “I will create the page fields” > Next
4. In the range, box, select G6:DF235 > Add > Next
5. Select New Worksheet > Finish.  A new worksheet will get created with a pivot table in it.
6. From the pivot table field list, uncheck Row and Column.  Only Value should remain checked.  The output should appear as shown in “Sheet2”
7. Double click on 6167 in the pivot table.  Another sheet (sheet3) will open up with data in three columns
8. Insert five columns after column A
9. Select range A2:A23588 and go to Data > Text to columns > Delimited > Next > Other > : > Next > Finish
10. Insert one column after column G
11. Select range G2:G23588 and go to Data > Text to columns > Delimited > Next > Other > : > Next > Finish
12. Give meaningful headings in range A1:I1

To exactly match the data on the “Desired Result” sheet, select range A1:I23588 on “Sheet3” and sort column E (CFA code) in ascending order.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Sir,
    Could you please help me out with a VBA code
    I have It like this
    Machine ID Part1 Part2 Part3
    A123 Spanner Gear12 Chain
    B3 O-ring oil-22
    C3 Chain

    I need like this way pls
    Machine ID Part
    A123 Spanner
    A123 Gear12
    A123 Chain
    B3 O-ring
    B3 oil-22
    C3 Chain

    Reply

  • Advanced filtering:

    Please follow the link where I have attached my document. I have a list of data in the tab named “Daily EV-DO Utilization”. I want to filter these data and only keep which are greater than 69.99%. In the second tab named “Filtered” I have shown the data that I need after applying filter. I have attached this file to make it easy to understand what I want to do.

    Link:
    http://www.mediafire.com/view/?1vvb379z677i4v5

    Reply

    • Hi,

      Try this

      1. Create a copy of sheet named “Daily EV-DO Utilization”. Name of the newly copied sheet is “Daily EV-DO Utilization – copy”
      2. Insert a column before column D and in cell D4, enter this formula =A4&”:”&B4&”:”&C4. Copy this formula down
      3. Insert a row before row 4 and in cell E3, enter this formula =E1&”:”&E2. Copy this formula to the right
      4. In cell D3, type Dummy
      5. Press Alt+D+P > Multiple Consolidation ranges > Next > I will create Page Fields > Next
      6. In the range, select D3:J503 > Next > New Worksheet > Finish
      7. From the Pivot Table Field List box, uncheck Row and Column (The Pivot Table should appear as you can see in Sheet1 of this workbook).
      8. Double click on 1626
      9. In the resulting worksheet, inserts two columns before column B
      10. Select range A2:A3001 and go to Data > Text to columns > Delimited > : > Finish
      11. Now insert one column before column E
      11. Select range D2:D3001 and go to Data > Text to columns > Delimited > : > Finish
      12. Give meaningful column headings in A1:F1
      13. Format last column as % ages (The Table should appear as you can see in Sheet2 of this workbook)
      14. Filter column F on >66.99%

      Hope this helps.

      Reply

        • Hi,

          After you have completed the steps of Data > Text to columns and Filtering, copy the filtered data to a new sheet and create a Pivot Table from that filtered data. Drag BTS Name, BSC and Cell ID to the Row labels. Drag Carrier and Sector to the columns labels and Values to the Value area of the Pivot Table.

          Reply

  • Dear Ashish Mathur,

    Many many thanks. I have got what I wanted to do by following your suggestion.
    Again thank you.

    Regards,
    Md.Rokibul Alam

    Reply

  • Very interesting article! Unfortunately, I struggled with apply this logic to the crosstab excel file I’m working with. In my case the majority of the fields are blank, which is accurate. I’m really trying to condense the data down just to the combinations that have values and make it easier to read/understand.

    The file is at:

    https://skydrive.live.com/redir?resid=111A7994941EE8A0!716&authkey=!ADTOZ9xY8Ys7uPA

    Can you help or offer suggestions?

    Thanks

    Reply

    • Hi,

      Thank you. This is what I did on your uploaded file:

      1. While on any cell of the User Group Permissions sheet, I pressed Alt+D+P > Multiple Consolidation Ranges > Next > I will create page Fields > Next
      2. Select range A1:DM1864 > Add > Next > New Worksheet > Finish
      3. In the Pivot Table created on the new sheet (sheet1), uncheck Rows and Columns in the Pivot Table Field List (only value should be ticked in the Pivot Table Field List box). The Pivot Table will now show a figure of 5352
      4. Double click on 5352 and a new sheet (sheet2) will get created with 3 columns and 216109 rows
      5. If you now wish to get rid of rows where the last column is empty, then you may just filter on blanks on the last column, right click > Delete > Table Rows
      6. Clear the filter

      You will be left with 5353 rows.

      Does this help.

      Reply

  • Perfect!!! That was exactly the solution I needed. Thank you very much for taking the time to assist me!

    Reply

  • I am trying to set up Marco from this:

    data1 a b c
    data2 w s
    data3 d q a b
    data4 a
    data5 g e k d j l

    and I want it into this:

    data1 a
    data1 b
    data1 c
    data2 w
    data2 s
    data3 d
    data3 q
    data3 a
    data3 b
    data4 a
    data5 g
    data5 e
    data5 k
    data5 d
    data5 j
    data5 l

    Can you help please? My aim is to look for a Macro solution(VBA) or formula solution, so in the future, I just need to copy and paste data dump and it will get transpose automatically.

    Thanks.

    Reply

      • Hi Ashish, can you please help me understand how to include a 3rd column that repeats the column heading that the value in column b is coming from? My data is laid out the same way, but I also need to know which column did the value for column B come from. Sounds like your solution is exactly what I need but with this small addition.
        Regards

        Reply

  • Ashish,

    I think I have a similar problem that I am trying to resolve within Excel 2007. I have peer values that I am trying to benchmark within my company, and I want to show the results within a single column. I also want to include a percentile rank, and to show where the peer results fall, but redact the identifying information from my results for all individuals except the one for whom I am providing my analysis.

    In addition, I would like to show my results as a single column chart that indicates the value & percentile rank for the individual, a gradient fill for the redacted results above my threshold (with a line indication and numeric/percentile result for the other peer values), and the overall column to show the range of ALL peer values for my company.

    I have a pivot table that includes the filtered values above my threshold. Is there a way that I can achieve what I am trying to accomplish? The closest comparison I can find is an Energy Star rating for appliances.

    Thank you so much for your help.

    Reply

    • Hi,

      I am not clear with your question. Please upload your file to SkyDrive and share the link of the uploaded file here. Please explain elaborately and also share the expected solution.

      Reply

      • https://drive.google.com/file/d/0B_hO3iGE0bD9TkxoUnRqZTJhSEk/edit?usp=sharing

        Ashish,

        Here is what I am trying to accomplish:

        I have one agency (CEEE) that I want to compare to their peers using data from a pivot table.

        I want to show the information in a single column with the following details:
        • The range of the values, which in this case runs from 0.0% to 1.7%.
        • The data above threshold marked out in a different color on the bar. For the analyzed data, my threshold is 1.0%, and I have 6 agencies that exceed it.
        • The agency for to be clearly indicated on the bar, with their rate percentage (1.7%), ranking (in this case, 2 of 49), and percentile.
        • I would also like to have my 5 other agencies that are above threshold indicated with a line and a rate with their identifying details concealed or redacted. However, I also need to embed the chart as a bitmap, and it may not be legible with the extra information.

        Again, what I am looking for is a single column, with two colors, that has full details for my CEEE agency and limited details for the range of values (bottom, top, and threshold percentages).

        The closest example I could think of was an appliance Energy Star rating chart, which shows an expected electricity cost for a model in comparison to all other similar ones. These are shown in as a single bar chart, with an indication as to where a particular model falls within the range. I want something similar, but in a column instead.

        Thank you again for your assistance.

        Reply

        • Hi,

          I am absolutely lost. In the Pivot Table appearing in column A:B of O Rate Pivots sheet, I have been able to get the rank (1-6) in column C. What else do you want. Please share your expected result and show me the exact chart you want.

          Reply

          • Hi,

            Thank you for clarifying. Let’s divide the problem into two parts – first is creating the Chart as shown in the “Chart Example” worksheet and next is creating the Graph as shown in the “Bitmap of Energy Star Chart” worksheet. Let’s leave the Graph for now – let’s only focus on the Chart of numbers.

            Kindly show the sample result (actual numeric result which you are expecting) in the Chart Example worksheet so that I can understand better.

            Before expending effort on solving this problem, I want to be absolutely clear with the result which you are expecting.

            Please upload the file with two worksheets – Actual data worksheet and Result worksheet.

            Thank you for your patience.

          • https://drive.google.com/file/d/0B_hO3iGE0bD9djJwckRuMktDeVU/edit?usp=sharing

            Hi Ashish,

            The first spreadsheet I sent you originally contained both pieces of information you are requesting. I am sorry for their poor quality, but I am trying to link spreadsheets to you via my home computer (with an ancient version of excel), and my work computer, which is blocking my access to the cloud files. Consequently, I am having a difficult time manipulating what I want to send to you and I cannot confirm what you are receiving, since I can’t see it.

            The newest link has a modified version of my last file, which also contained my data source and the results spreadsheet. I had the results worksheet formerly named “O Rate Pivots.” The pivot is pulls out values that exceed 1.0%, which is noted as a decimal in the source worksheet, but my pivot shows the results as a percentage. You should also know that the number is not calculated from my data–it’s provided to me from other company systems.

            Again, my concern is simply presentation of the data in a spectrum.

            I am hopeful the second sheet and the additional information helps.

            Thank you, again.

            Susan.

          • Hello again, Ashish!

            I know I am busy making you crazy with my Excel problems, but I thought it might be fun to throw another monkey in the mix. 🙂

            Do you think it would be possible to consolidate more than one of these column charts together? When I write my analyses, I usually group more than one measure within my discussions. For example, the first section of my report covers my F, W, O, N, & C rates for my discussions, which all (conveniently!), have different thresholds.

            Within my first section, the thresholds are as follows:
            • F rate: 10.5%,
            • W rate: 6.5%,
            • O rate: 1.0%,
            • N rate: 7.5%, &
            • C rate: 5.0%.

            I would assume I would need to lose detail from the main chart, but I could speak to the specifics, i.e., ranking, percentile, & agency rate, as bulleted information below the graphic. If the chart could show the columns in 2 segments, with the agency rate marked with a line for each rate, & bottom/top rates (over all 49 agencies), and 5 bars across (one for each measure), that would be great.

            Thank you again for your continued assistance.

            Susan.

  • I think my response may not have been as clear as it could have been. When I pivoted my data, I used my Agency code as my Row Label, a Sum of O Rate for my Values, and a Report Filter of O Rate, and I selected all rates over 0.01.

    I am hopeful that addresses your question more fully.

    Thank you.

    Reply

  • Hi Ashish,

    I have a data in which the employees training records are mantained in a vertical format eg
    ashish communication skills
    ashish presentation skills
    ashish MS excel
    Abhay Communication
    Abhay Leadership Training

    There are about 700 employees whose training record are listed in thie above fashion. I need to remove the duplication of names and present the data in the horizontal format like (each program in different cell)
    Ashish – communication skills presentation skills ms excel
    abhay – communication leadership training.

    I think “transpose” wont work as the data size is huge
    Pls suggest if this can be done

    Reply

  • Hi Ashish. I was on the Microsoft forum for excel and you asked me to post my question here. So here it goes:

    I have data as output which are dumped into an excel spreadsheet in the following form:

    https://docs.google.com/spreadsheets/d/1i6Mb8p-WVPV2SbKYJIzDDl6VCtY9oa4yStjVvg9ICFM/edit?usp=sharing

    Question1: I would like to automatically plot those data each time I have a new output. However, I don’t know beforehand how many rows or how many columns the output will have. I have a template with plots for my data, however, as of now, it only selects the current number of rows and the current number of columns. So when there are more than 5 columns or more than the current number of rows, the data outside the initial range are left out of the plot. Is there a quick and efficient way to handle this problem (i.e. to make sure excel selects the WHOLE new range and not just the previous range for plotting)?

    Question2: I also need to produce some tables in other worksheets. typically, I use the transpose() formula, or = formula when I want to select an array. In the template, I need to use ‘CTRL+SHIFT+ENTER’. Otherwise I get an error. But this shortcut is not automatically applied with new outputs. I have to manually go in each array and double click a cell, and use ‘CTRL+SHIFT+ENTER’. Which is unacceptable for any other user. Is there a better way to have my arrays transposed or pasted without having to always manually use the ‘CTRL+SHIFT+ENTER’ shortcut?

    Reply

    • Hi,

      Your requirement is completely different from the one you were talking about at this link – anyways, to answer your first question, try this

      1. Create your Graph from your dataset in range A1:E7
      2. With A1:E7 selected, press Ctrl+T . OK

      Now as and when you add data by rows or columns, the Graph will keep updating.

      I am not clear with your second question.

      Reply

  • Hi Ashish,
    I need to convert a matrix into .data format so that I can use it for SOM toolbox. I need to label the features of the data through u-matrix in matlab. I am doing a project on intrusion detection system and using kdd data set with attacks for that when am using the data set with som toolbox the label column is empty and I dont know how to figure that out. if you have any idea about this and if you can help.

    Reply

  • Hi I am not getting how to label the clusters in Unified matrix of self organising map . I am using som toolbox and kdd cup 99 data set.

    Reply

  • I have Excel 2007 and, sadly, connect use the Power Query tool. I walked through the workflow that you gave for “All versions of MS Excel”. Everything made sense except crunching the multiple columns (row labels from Pivot Table) with concatenate into cells G7:G35. So I downloaded the linked Excel file at the top of this page and went to the “Source data adjusted”, only to sadly discover that G7:G35 does not have this nifty concatenating-crunching bit done. I’m lost as to what to do.

    Could you please add this to the Excel file and repost? Thanks.

    Reply

  • Hi Ashish,

    My question is similar to Christine who has posted the following question

    “I am trying to set up Marco from this:

    data1 a b c
    data2 w s
    data3 d q a b
    data4 a
    data5 g e k d j l

    and I want it into this:

    data1 a
    data1 b
    data1 c
    data2 w
    data2 s
    data3 d
    data3 q
    data3 a
    data3 b
    data4 a
    data5 g
    data5 e
    data5 k
    data5 d
    data5 j
    data5 l”

    I have downloaded the macro and it works very well for smaller data.

    But the data that I have is huge one. My data range is from C1:AJ30008

    Everytime I run the macro I get the error “Run time error ‘6’: Overflow”

    Can you pls help me on this?

    I am very new to excel macros. I dont know edit the macro codes!

    Pls help me Ashish.

    Thanks in advance.

    Reply

  • Dear sir,

    Pls share macro for converting a list with 3 columns into cross table/matrix.

    like
    ID Prize Year
    A X 9
    A Y 10
    B X 9
    B Y 9
    B Z 10

    into

    ID 9 10
    A X Y
    B X, Y Z

    Reply

  • Hi Ashish,

    I have a matrix that has %s in the cells and a quantity at the end. I need to convert to rows. The matrix is:

    MatA MatB MatC Quantity
    Mat1 20 0 80 1000
    Mat2 50 50 0 2000
    Mat3 15 35 50 1000

    I need to convert this to rows as:

    123 ABC 20 200
    123 IJK 80 800
    456 ABC 50 1000
    456 DEF 50 1000
    789 ABC 15 150
    789 DEF 35 350
    789 IJK 50 500

    Please help if possible. Thanks a million.

    Reply

  • Sir Mathur,

    I need your kind help if possible. I have 3 columns of data that I need to transpose using formula or macro. From the below example I need it to be 8 columns to look like this. I have over 20,000 rows of data to convert. Thank you in advance.

    Connector Gender Connector Quantity Connector Shape Mounting Location Terminal Gender Terminal Quantity Terminal Type
    ALS1041 Female 1 Rectangular Wheel Male 2 Blade
    ALS1043 Male 1 Rectangular Wheel Female 2 Blade
    ALS1044 Female 1 Rectangular Wheel Male 2 Blade

    DATA EXAMPLE to CONVERT
    ALS1041 Connector Gender Female
    ALS1041 Connector Quantity 1
    ALS1041 Connector Shape Rectangular
    ALS1041 Mounting Location Wheel
    ALS1041 Terminal Gender Male
    ALS1041 Terminal Quantity 2
    ALS1041 Terminal Type Blade
    ALS1043 Connector Gender Male
    ALS1043 Connector Quantity 1
    ALS1043 Connector Shape Rectangular
    ALS1043 Mounting Location Wheel
    ALS1043 Terminal Gender Female
    ALS1043 Terminal Quantity 2
    ALS1043 Terminal Type Blade
    ALS1044 Connector Gender Female
    ALS1044 Connector Quantity 1
    ALS1044 Connector Shape Rectangular
    ALS1044 Mounting Location Wheel
    ALS1044 Terminal Gender Male
    ALS1044 Terminal Quantity 2
    ALS1044 Terminal Type Blade

    Reply

  • Sir Mathur,

    You are absolutely amazing. Thank you so much for your help. You’re the best! Take care

    Reply