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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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

robofixAuthor Commented:
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?
Patrick MatthewsCommented:
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
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robofixAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.