Effectively rearrange large dataset

Dear member,

I am trying to effectively rearrange my Market-to-Book ratio data so that I can easily paste it into Stata and use it for my regression analysis. Currently, Datastream provided me with output in the following way:

Hulp.png

I.e. in the first column it shows all years and then every new collumn is the output per company. However, I need to rearrange my data so that it gives the company name in the first collumn, the year in the second and then for that year the respective value in the third collumn. A short example is:


NAME YEAR MBTV
BARCLAYS 2004 0.26
BARCLAYS 2005 0.28
BARCLAYS 2006 0.29
BT GROUP 2004 0.25
BT GROUP 2005 0.29
BT GROUP 2006 0.31
BT GROUP 2007 0.12


etc.

How can I effectively rearrange this (due to a dataset of approximately 40K companies with 14 firm-year observations each)?

Thank you for your help!!

See attached: sample data in column A:C with output in E:G

in E2

=INDEX($B$1:$C$1,INT((ROWS($1:1)-1)/14)+1)

in F2

=MOD(ROWS($1:1)-1,14)+2004

in G2

=INDEX($B$2:$Z$15,MOD(ROWS($1:1)-1,14)+1,MATCH($E2,$B$1:$Z$1,0))

Set Calculation to Manual before inserting the formulae. Then res-set to Automatic.

Formulas==>Calculation Options