Convert one column to multiple columns based on column values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have:
FIPS | State | Area | Name | Value | Year |
1 | 1 | 1 | A | val1 | 1 |
1 | 1 | 2 | B | val2 | 2 |
1 | 1 | 3 | C | val3 | 3 |
2 | 2 | 4 | D | val4 | 2 |
2 | 2 | 1 | A | val5 | 3 |
2 | 2 | 2 | B | val6 | 4 |
3 | 3 | 3 | C | val7 | 1 |
3 | 3 | 4 | D | val8 | 2 |
3 | 3 | 5 | A | val9 | 9 |
And I would like to get here:
FIPS | State | Area | A | B | C | D | Year |
1 | 1 | 1 | Val1 | Val2 | Val3 | Val4 | 1 |
1 | 1 | 2 | Val5 | Val6 | Val7 | Val8 | 2 |
Any help appreciated... Nothing in previous posts like this.
Solved! Go to Solution.
- Labels:
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
The data sample you have here doesn't seem to exactly translate to what you're looking for, so i'm assuming you have more data with a value like
FIPS | State | Area | Name | Value | Year |
1 | 1 | 1 | B | val2 | 1 |
If that data doesn't exist, i'm hoping you can give me some more information.
The Cross-Tab tool should accomplish what you want.
If you Group By the fields FIPS, State, Area, and Year, then your Column Name can be "Name" and Value can be "Value".
Based on the sample dataset, you would then get an output something like this:
FIPS | State | Area | Year | A | B | C | D |
1 | 1 | 1 | 1 | val1 | |||
1 | 1 | 2 | 2 | val2 | |||
1 | 1 | 3 | 3 | val3 | |||
2 | 2 | 1 | 3 | val5 | |||
2 | 2 | 2 | 4 | val6 | |||
2 | 2 | 4 | 2 | val4 | |||
3 | 3 | 3 | 1 | val7 | |||
3 | 3 | 4 | 2 | val8 | |||
3 | 3 | 5 | 9 | val9 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much! I selected First as Method and it looks OK (there seem to be no empty cells)!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Awesome! Glad I could help!
If you don't trust your data, you can leverage the Concatenate option and do some nifty things like counting your delimiter character in order to prove out that you never "double up" in your source.
