Transforming a table into matrix in Excel
By John Campbell •
So I basically have something like the situation in the picture where I need to transform the table in A into a matrix like in B. The row/column headers are not necessary, just the Matrix. I would be open to a tool other than Excel if it would be simpler, like Matlab,SQL,Python or R. Is it possible to do this in a straight forward manner for a large amount of rows (several thousand)
2 Answers
How it works:
- Select Source data
S28:U37. - From Insert TAB hit Pivot Table.
- Better create PT in Existing Sheet.
- Use Company, Transaction Amount & Row Number in ROWS, VALUES & in COLUMNS (Check attached screen shot).
- Now select any cell of Pivot Table then from Design TAB, click Grand Totals, and hit On For Column Only.
N.B.
- You may adjust Grand Totals as your need.
- Grand Totals for Columns has been added, since you have asked for, through comments above.
Once again select PT and Right click.
Now from Popup menu hit Pivot Table Options.
Now from the displayed dialogue box hit TAB Display, then un-check Display Field Captions and Filter drop downs.
- Finish with Ok.
Note:
- If you don't need the Grand Totals, reset the settings for
Off for Rows and Columns, you get the result as shown below.
If you have Excel 2010+, you can use Power Query also known as Get&Transform
Data Tab→Get & Transform→From Table/Range- Select the
Row nrcolumn and Pivot Column- Values Column:=
Transaction Amount - Advanced Options:
Don't Aggregate
- Values Column:=
M-Code
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Transaction amount", Int64.Type}, {"Row nr", Int64.Type}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Row nr", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Row nr", type text}}, "en-US")[#"Row nr"]), "Row nr", "Transaction amount"), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Company", "Company/Row"}})
in #"Renamed Columns"Results
