Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2010-10-17
Medium Priority
Last Modified: 2012-05-10
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
Question by:robofix
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 33923621
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


Author Comment

ID: 33923664
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?
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33924358
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
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 33924950
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


Author Closing Comment

ID: 33933286
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question