Modeling tables into one panel table

Hi everyone,

first of all compliments to all the great advice you provide! Always helpful.

Unfortunately I couldn't find any specific thread on this topic (maybe 'cause it has a veeery simple solution I can't figure out). This is where I need your help:

I have several tables that represent one variable for several countries for several years, something like this:

(didn't get to fit the dashes under the years, sorry!)

x1
Country/Year 2000 2001 2002 2003 2004
ITA - - - - -
DEU - - - - -
GBR - - - - -
USA - - - - -

x2
Country/Year 2000 2001 2002 2003 2004
ITA - - - - -
DEU - - - - -
GBR - - - - -
USA - - - - -

x3
Country/Year 2000 2001 2002 2003 2004
ITA - - - - -
DEU - - - - -
GBR - - - - -
USA - - - - -

and so on (10 variables, ca 80 countries for ca 50 years).
I need to organize them in a long-form panel table to run some regressions, something like this:

Country Year x1 x2 x3 x4
ITA 2000 - - - -
ITA 2001 - - - -
ITA 2002 - - - -
ITA 2003 - - - -
ITA 2004 - - - -
DEU 2000 - - - -
DEU 2001 - - - -
DEU 2002 - - - -
DEU 2003 - - - -
DEU 2004 - - - -
GBR 2000 - - - -
GBR 2001 - - - -
GBR 2002 - - - -
GBR 2003 - - - -
GBR 2004 - - - -
USA 2000 - - - -
USA 2001 - - - -
USA 2002 - - - -
USA 2003 - - - -
USA 2004 - - - -

Any suggestion on how to do so without having to spend weeks copying and pasting?

Here is a sample as suggested: Workbook1.xlsx

Help is much appreciated.

Oel

Its better you attach a sample file

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Will there always be 4 sheets and then the summary sheet? Or are there chances of having more sheets?

Also, will there be any values in the columns for the years?