Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-10-17
5
Medium Priority
?
32,145 Views
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 ....
date1
date2
date3
...

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
Rob
0
Comment
Question by:robofix
  • 2
  • 2
5 Comments
 
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

    Worksheets.Add
    [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
        Next
    Next

    MsgBox "Done"

End Sub

Open in new window

0
 
LVL 3

Author Comment

by:robofix
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?
0
 
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
0
 
LVL 50

Accepted Solution

by:
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






Book1.xlsx
3
 
LVL 3

Author Closing Comment

by:robofix
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

886 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