Economics Database Transpose/Rearange

[viktor4e]

Hi everyone,

Very frequently I have economic databases in the following format in excel:

YEAR US India China
2010 100 200 400
2011 300 280 120
2012 120 300 500
2013 200 50 120


I would like to have a macro to transform the data in the following format regardless of the number of columns and rows in the initial database (sometimes the time period is 1961-2014 and I have as many as 100 countries):

Year Country Value
2010 US 100
2011 US 300
2012 US 120
2013 US 200
2010 India 200
2011 India 280
2012 India 300
2013 India 500
2010 China 400
2011 China 120
2012 China 500
2013 China 120

Any suggestions?
Much appreciated!

 

[MickG]

Try this:-
Results sheet2

[COLOR="Navy"]Sub[/COLOR] MG30Jun04
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Lst         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 2
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] Ac = 1 To Lst - 1
    [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
        .Range("A1:C1").Value = Array("Year", "Coiuntry", "Value")
        .Cells(c, 1).Resize(Rng.Count).Value = Rng.Value
        .Cells(c, 2).Resize(Rng.Count).Value = Rng.Offset(-1, Ac)(1)
        .Cells(c, 3).Resize(Rng.Count).Value = Rng.Offset(, Ac).Value
        c = c + Rng.Count
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[viktor4e]

Thank you so much Mick!
The macro works perfectly.

Cheers

Try this:-
Results sheet2

[COLOR=Navy]Sub[/COLOR] MG30Jun04
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Lst         [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Ac          [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] c           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
c = 2
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]For[/COLOR] Ac = 1 To Lst - 1
    [COLOR=Navy]With[/COLOR] Sheets("Sheet2")
        .Range("A1:C1").Value = Array("Year", "Coiuntry", "Value")
        .Cells(c, 1).Resize(Rng.Count).Value = Rng.Value
        .Cells(c, 2).Resize(Rng.Count).Value = Rng.Offset(-1, Ac)(1)
        .Cells(c, 3).Resize(Rng.Count).Value = Rng.Offset(, Ac).Value
        c = c + Rng.Count
    [COLOR=Navy]End[/COLOR] With
[COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick