Sorting Data From One Column into Multiple Columns

[Justin Hoffmann]

Hello,

In my work, I download a lot of statistical data from government sources.
Unfortunately, the data is usually presented in a way that is not easy to
manipulate or work with. Right now, I am working employment data for the 50
U.S. states.

The spreadsheet I currently have essentially has three really long columns.

The first column is the state name (United States, Alabama, Alaska,
Arkansas, Arizona, etc.)

Second column is the the industry (Agriculture, manufacturing, retail, etc.)

Third column is the data for employment.


Rather than having all of the data in long (6000+ rows), I want to place
each of the states in to their own column. So the final table would look
something like this:

First column: Industry
Second column: U.S. data
Third Column: Alabama data
Fourth column: Alaska data
Fifth column: Arkansas data

And so on until the last state


Is there an easy way to accomplish this rather than using cut and paste?

Thanks for your help and suggestions.

 

[Guest]

Take a look at Pivot Tables....I think they'll give you the flexibility
you're looking for.

Here's how to set it up:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Industry field here
ROW: Drag the State field under the Industry field
DATA: Drag the EmploymentData field here
If it doesn't list as Sum of EmploymentData...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...and you're done!

To refresh the Pivot Table, just right click it and select Refresh Data

Post back with any questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Justin Hoffmann]

Thanks. I'll give that a try.

n article (e-mail address removed), Ron Coderre at

Take a look at Pivot Tables....I think they'll give you the flexibility
you're looking for.

Here's how to set it up:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Industry field here
ROW: Drag the State field under the Industry field
DATA: Drag the EmploymentData field here
If it doesn't list as Sum of EmploymentData...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...and you're done!

To refresh the Pivot Table, just right click it and select Refresh Data

Post back with any questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Hello,

In my work, I download a lot of statistical data from government sources.
Unfortunately, the data is usually presented in a way that is not easy to
manipulate or work with. Right now, I am working employment data for the 50
U.S. states.

The spreadsheet I currently have essentially has three really long columns.

The first column is the state name (United States, Alabama, Alaska,
Arkansas, Arizona, etc.)

Second column is the the industry (Agriculture, manufacturing, retail, etc.)

Third column is the data for employment.


Rather than having all of the data in long (6000+ rows), I want to place
each of the states in to their own column. So the final table would look
something like this:

First column: Industry
Second column: U.S. data
Third Column: Alabama data
Fourth column: Alaska data
Fifth column: Arkansas data

And so on until the last state


Is there an easy way to accomplish this rather than using cut and paste?

Thanks for your help and suggestions.