Re-arranging columns and rows

Hi,

I am working with several dozens of Excel files with thousands of variables, but in order to work with them, a few changes must be made that I am not sure how to tackle.

Problem:


I am looking at migration statistics in India. The data is broken up state into what you might say is like a 4000 row x 25 column grid of data. For each state, there are rows that break down how people moved and the age groups for each method movement, effectively giving me 125 rows per state. The columns are then arranged by number of male/female, literate/illiterate, etc.

What I Need:

I need the data to be rearranged into a 35 row x 10,000 column grid of data (an estimate). For rows I want the only variable to be the states, and then have thousands of columns displayed all of the information. So it would be a state and then display, the male/female ratio under a certain age group, etc.

How would I do this? I have to do this for several files so it would take forever to do manually.

Just realized that Word 2003 has a 256 column limit.

In that case, I guess I will need to have about 10,000 rows and 35 columns.

The data must be in this manner so that in can be imported Stata and easily sorted for research.


So to recap, at the moment I have 4000 rows and 25 columns. Each state has about 125 rows, which separates it into migration patterns and then further by age groups, create 125 rows per state. As for column, variables such as male/female, literate/illiterate and education level are presented.

What I will need is to have 35 columns that are just the states. Then I will thousands of rows broken down into migration patterns and age, but then also by the variables that were formerly by columns.

How do I approach this and does this make any sense?

Have you thought about using a Pivot Table for this Analysis?