Convert one column to multiple columns based on column values

[mpn]

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.

[Claje · Accepted 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

[mpn]

Thank you so much! I selected First as Method and it looks OK (there seem to be no empty cells)!

[Claje]

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.