Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 34101
  • Last Modified:

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 ....
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
robofix
Asked:
robofix
  • 2
  • 2
2 Solutions
 
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

    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
 
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?
0
 
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
0
 
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






Book1.xlsx
3
 
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now