How to "flatten" or "collapse" a 2D Excel table into 1D?

Question

I have a two dimensional table with countries and years in Excel. eg.

        1961        1962        1963        1964 USA      a           x            g           y France   u           e            h           a Germany  o           x            n           p 

I'd like to "flatten" it, such that I have Country in the first col, Year in the second col, and then value in the third col. eg.

Country      Year       Value USA          1961       a USA          1962       x USA          1963       g USA          1964       y France       1961       u               ... 

The example I present here is only a 3x4 matrix, but the real dataset i have is significantly larger (roughly 50x40 or so).

Any suggestions how I can do this using Excel?

Answer 1

You can use the excel pivot table feature to reverse a pivot table (which is essentially what you have here):

Good instructions here:

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

Which links to the following VBA code (put it in a module) if you don't want to follow the instructions by hand:

Sub ReversePivotTable() '   Before running this, make sure you have a summary table with column headers. '   The output table will have three columns.     Dim SummaryTable As Range, OutputRange As Range     Dim OutRow As Long     Dim r As Long, c As Long      On Error Resume Next     Set SummaryTable = ActiveCell.CurrentRegion     If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then         MsgBox "Select a cell within the summary table.", vbCritical         Exit Sub     End If     SummaryTable.Select     Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8) '   Convert the range     OutRow = 2     Application.ScreenUpdating = False     OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")     For r = 2 To SummaryTable.Rows.Count         For c = 2 To SummaryTable.Columns.Count             OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)             OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)             OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)             OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat             OutRow = OutRow + 1         Next c     Next r End Sub 

-Adam

Answer 2

@Adam Davis's answer is perfect, but just in case you're as clueless as I am about Excel VBA, here's what I did to get the code working in Excel 2007:

1. Open the workbook with the Matrix that needs to be flattened to a table and navigate to that worksheet
2. Press Alt-F11 to open the VBA code editor.
3. On the left pane, in the Project box, you'll see a tree structure representing the excel objects and any code (called modules) that already exist. Right click anywhere in the box and select "Insert->Module" to create a blank module file.
4. Copy and paste @Adman Davis's code from above as is into the blank page the opens and save it.
5. Close the VBA editor window and return to the spreadsheet.
6. Click on any cell in the matrix to indicate the matrix you'll be working with.
7. Now you need to run the macro. Where this option is will vary based on your version of Excel. As I'm using 2007, I can tell you that it keeps its macros in the "View" ribbon as the farthest right control. Click it and you'll see a laundry list of macros, just double click on the one called "ReversePivotTable" to run it.
8. It will then show a popup asking you to tell it where to create the flattened table. Just point it to any empty space an your spreadsheet and click "ok"

You're done! The first column will be the rows, the second column will be the columns, the third column will be the data.