Reshaping data excel to panel data

Hello,

I am new here and currently working on my Finance thesis.
I have several variables, which have to be reshaped a bit.
See the attachment for the data.
On the left is the original and on the right is the way i would like it.
I have done the example by hand, but is there an easy way to change it?

Thanks Bob

Another approach that doesn't use volatile functions:

I inserted a couple of blank columns to move your sample output further to the right, then used these formulae in the cells stated:

F2: =INDEX(A:A,INT((ROWS($1:1)-1)/3)+2)

G2: =RIGHT(INDEX($B$1:$D$1,MOD(ROWS($1:1)-1,3)+1),4)

H2: =VLOOKUP(F2,$A:$D,MOD(ROWS($1:1)-1,3)+2,0)

Copy down as far as required.

Hope this helps.

Pete

Originally Posted by Glenn Kennedy

Column H =OFFSET($A$2,INT((ROWS($1:1)-1)/3),,,)
Column I =2013+MOD(ROWS($1:1)-1,3)
Column J: =OFFSET($B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3),,)

Thanks lad!!! You saved me a lot of sleep