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

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?

Solution 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

Solution 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.

Solution 3:

In Excel 2013 need to follow next steps:

• select data and convert to table (Insert -> Table)
• call Query Editor for table (Power Query -> From Table)
• select columns that contain years
• in context menu select 'Unpivot Columns'-command.

Support Office: Unpivot columns (Power Query)

In Excel 2016, Power Query is called Get & Transform and it is found in the Data tab.

Solution 4:

Flattening a data matrix (aka Table) can be accomplished with one array formula¹ and two standard formulas.

The array formula¹ and two standard formulas in G3:I3 are is,

=IFERROR(INDEX(A$2:A$4, MATCH(0, IF(COUNTIF(G$2:G2, A$2:A$4&"")<COUNT($1:$1), 0, 1), 0)), "")
=IF(LEN(G3), INDEX($B$1:INDEX($1:$1, MATCH(1E+99,$1:$1 )), , COUNTIF(G$3:G3, G3)), "")
=INDEX(A:J,MATCH(G3,A:A,0),MATCH(H3,$1:$1,0))

Fill down as necessary.

While array formulas can negatively impact performance due to their cyclic calculation, your described working environment of 40 rows × 50 columns should not overly impact performance with a calculation lag.

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}