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.
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 |
Thank you so much! I selected First as Method and it looks OK (there seem to be no empty cells)!
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.