We help IT Professionals succeed at work.

How to reshape data from WIDE to LONG format in Excel?

Dear all,
in Excel (2010), who knows the smartest way to reshape a dataset of GDP values in WIDE format, e.g.
           country1  |  country2 | country3 ....

to LONG format, e.g.
date1 country1 GDP11
date2 country1 GDP21
date1 country2 GDP12
date2 country2 GDP22
(order of rows doesn't matter) ?

I know some statistics programs like Stata do this with the reshape command, and you can obviously also stack the columns of the wide version so as to produce a long table in VBA, but I'd like to see if you can do this in plain Excel. If this is not possible with on-board excel commands, a generic VBA-script or Add-in being able to do this without much customization would be considered a second-best solution.

The long table will afterwards be loaded into a MySQL database table.

Best regards
Watch Question

Top Expert 2010
Rob,This can be done with formulas, but I find it easier to use VBA.  Something like the code below should do it.Patrick
Sub Normalize()

    Dim LastR As Long
    Dim LastC As Long
    Dim arr As Variant
    Dim DestR As Long
    Dim RowCount As Long
    Dim ColCount As Long

    With Worksheets("Sheet1")
        LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
        LastC = .Cells(1, .Columns.Count, "a").End(xlToLeft).Column
        arr = .Range(.[a1], .Cells(LastR, LastC)).Value
    End With

    [a1:c1].Value = Array("Date", "Country", "GDP")
    DestR = 1

    For RowCount = 2 To LastR
        For ColCount = 2 To LastC
            If arr(RowCount, ColCount) <> "" Then
                DestR = DestR + 1
                Cells(DestR, 1) = arr(RowCount, 1)
                Cells(DestR, 2) = arr(1, ColCount)
                Cells(DestR, 3) = arr(RowCount, ColCount)
            End If

    MsgBox "Done"

End Sub

Open in new window


Ok, I see, in fact I've solved the issue similarly in the past.
So there's not built-in trick in Excel (w/o VBA) to do that for you?
Further suggestions, anyone?
Top Expert 2010

As I mentioned previously, you can do this with formulas.For example, assuming your original data are on Sheet1, then on another sheet enter Date, Country, and Value in Row 1, and then use this for A2:=INDEX(Sheet1!A:A,1+ROW(A1)-(MATCH(10^200,Sheet1!A:A)-1)*(ROUNDUP(ROW(A1)/(MATCH(10^200,Sheet1!A:A)-1),0)-1))B2:=INDEX(Sheet1!$1:$1,1,ROUNDUP(ROW(A1)/(MATCH(10^200,Sheet1!A:A)-1),0)+1)C2:=INDEX(Sheet1!$1:$1048576,1+ROW(A1)-(MATCH(10^200,Sheet1!A:A)-1)*(ROUNDUP(ROW(A1)/(MATCH(10^200,Sheet1!A:A)-1),0)-1),ROUNDUP(ROW(A1)/(MATCH(10^200,Sheet1!A:A)-1),0)+1)Copy those formulas down as far as needed.Patrick
Most Valuable Expert 2011
Awarded 2010
Hello Rob,

you can do this without VBA or formulae by creating a reverse pivot table.

Using the attached file as an example:

- Click anywhere within the data table
- hit the key combination Alt - d - p to bring up the Pivot Table Wizard (which is not on the ribbon)
- select "Multiple consolidation ranges" and click Next
- select "I will create the page fields" and click Next
- select your data table (in the example it's A1:E11)  and click Next
- select the location and click Next

Now you will see a pivot table that looks just like your data table, but we're not finished.
- click the Pivot table and in the field list uncheck Row and Column (and uncheck any Page Field, if present)
- now select the cell with the single remaining total number and double click it.

The result is a new sheet with your data laid out one item per row.

cheers, teylyn



thanks to both for their excellent answer.
matthew's solution worked ad hoc and did the job for me, but next time i'll try teylyn's suggestion with the reverse pivot table, which was the kind of solution I was looking for, already knowing the VBA-kind of way to stack columns.