Pls Help, creating panel data by converting several columns into one

Hi guys,

First of all - great forum, have been following passively for some time and hoped to find help with this, no luck though.

My problem:
I have downloaded national economic data from IMF with several countries for several years. I need several variables and must extract them individually so I a need a clever solution to my problem.
I currently have data that is sorted like below. The "-" indicates a numerical gdp value for the given country for the given year.;
(FYI I could not get the "-" to align beneath 2001-2004 and year respectively.)


Country 2001 2002 2003 2004
x - - - -
y - - - -
z - - - -

The identifier here is country, and the data is as such structered as cross sectional data, with each yearly account of gdp being a variable.
Now, I need this data as panel data, organized as below;

Country year gdp
x 2001 -
x 2002 -
x 2003 -
x 2004 -

I sort of say, need to "flip it". As I have 180 countries and 11 years, and need to do this with 2-3 variables, I need either a formula or a VBA to run.

Can anyone help me here?

This is really great - just took a look at the formula - looks exactly as what I need to. Perfect.
Its amazing what excel can do - you just need to ask the right persons.
Thanks again.

Brilliant - exactly what i needed thank you guys

Great tip, but how do you get the formulas working for more variables? I have 10 variables for 5 years.

Like this:

Company Year Non current liabilitie Current liabilities Total asset Tangible fixed assets x....
LACO AS 2010
LACO AS 2009
LACO AS 2008
LACO AS 2007
LACO AS 2005
AWILHELMSEN AS 2010
AWILHELMSEN AS 2009
AWILHELMSEN AS 2008
AWILHELMSEN AS 2007
AWILHELMSEN AS 2005
AWILHELMSEN HOLDING AS 2010
AWILHELMSEN HOLDING AS 2009
AWILHELMSEN HOLDING AS 2008

Hi,

New to the forum - thanks a lot to all the experts, it is a great help. Just as Pettering above I would like to know if there is a way to include more variables?

Cheers,
Stu

You just saved me 10 days of work! Thank you so much MarvinP!!

Originally Posted by Haseeb A

Hello, kaspernrasmussen;

Also try this with Formulas. See the attached.

Thanks for this excellent, user friendly solution.

Thank you so much for the great solutions posted. They are very useful..

Thank you Marvin. That saved up a lot of time.

That is perhaps the most amazing and helpful macro ever.


Thankful econometrics student.

Thank you so much MarvinP for the macro that you have created to create panel data. It is a great help, good day.

HI
Have u got this query solved, i also have same kind of data.

hi

have u got your query solved, how to do this as i also have same kind of data.

Thank you Haseeb, you literally saved the day!