transforming a long dataset into a wide format

[dmunte]

hi guys,


my problem is similar to the one outline here except for the fact that my only variable is GDP
basically, my dataset looks like:

region year log_gdp
region1 2000 4.23
region1 2001 4.29
region1 2002 4.27
region2 2000 4.19
region2 2001 4.22
region2 2002 4.24
....

and i would like to make it like this:

region/year 2000 2001 2002
region1 4.23 4.29 4.27
region2 4.19 4.22 4.24

unfortunately my programming skills are not so advanced and didn't manage to modify the code available in the other thread.

i would greatly appreciate any help with this problem, as copying and pasting the data is very time consuming.


thanks,
dan

[ragulduy]

Sub macro_1()
Dim ws1, ws2, count1, count2
Set ws1 = ActiveSheet
Set ws2 = Sheets.Add
With ws2
    .Range("A1") = "Region/Year"
    .Range("B1") = 2000
    .Range("C1") = 2001
    .Range("D1") = 2002
End With
count1 = 2
count2 = 2
Do Until ws1.Range("A" & count1) = ""
    ws2.Range("A" & count2) = ws1.Range("A" & count1)
    ws2.Range("B" & count2) = ws1.Range("C" & count1)
    ws2.Range("C" & count2) = ws1.Range("C" & count1 + 1)
    ws2.Range("D" & count2) = ws1.Range("C" & count1 + 2)
    count1 = count1 + 3
    count2 = count2 + 1
Loop
End Sub