Sorting Data From One Column into Multiple Columns

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.

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" wrote:

> 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.
>
>

Thanks. I'll give that a try.

n article [email protected], Ron Coderre at
[email protected] wrote on 7/12/06 10:56 AM:

> 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" wrote:
>
>> 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.
>>
>>

Thanks. I'll give that a try.

n article [email protected], Ron Coderre at
[email protected] wrote on 7/12/06 10:56 AM:

> 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" wrote:
>
>> 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.
>>
>>