Reshaping Panel Data (long to wide)

[brian_griffin]

Hello,

I would like to transform and reshape my data set from the long to the wide format.

Unluckily, in the original data set the variables are listed below each other in one column.

The orignal data set has the following format :
[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]VAR[/TD]
[TD="width: 64"]Year[/TD]
[TD="width: 64"]Value[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]A[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]A[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]A[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]B[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]B[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]B[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]B[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]B[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]C[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]C[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]C[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]


I would to like to transform the data to the following format:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Year[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there any feasible way to achieve this ?

I do not have any idea how to proceed.

Thanks in advance,
Brian

 

[Andrew Poulsom]

Can't you use a Pivot Table?

Excel 2003
	F	G	H	I	J
1	Sum of Value		VAR
2	ID	Year	B	A	C
3	1	2010	3	5
4		2011	4	9
5		2012		7
6	2	2010		3	2
7		2011		2	3
8		2012		8	4
9	3	2010	5	4
10		2011	8	2
11		2012	1	1
Sheet2

 

[MickG]

Try this:-
Results start "F1"

[COLOR="Navy"]Sub[/COLOR] MG22Nov14
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] twn         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count, 1 To 5)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    twn = Dn & Dn.Offset(, 2)
    Col = Asc(Dn.Offset(, 1)) - 62
    [COLOR="Navy"]If[/COLOR] Not .Exists(twn) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        .Add twn, n
        Ray(n, 1) = Dn: Ray(n, 2) = Dn.Offset(, 2)
        Ray(n, Col) = Dn.Offset(, 3)
    [COLOR="Navy"]Else[/COLOR]
        Ray(.Item(twn), Col) = Dn.Offset(, 3)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
c = .Count
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Range("F1")
.Resize(, 5) = Array("ID", "Year", "A", "B", "C")
.Offset(1).Resize(c, 5) = Ray
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[brian_griffin]

thank you very much!

Your two reponses were of great help!

best
brian