Manipulate Input File into Specific Output Format

[CindyA]

I have an input file that needs to be manipulated into a specific output format. I cannot figure out the best way to accomplish this. The input/output file is below, I could not upload it. Basically I need to summarize the 3 amounts for each entity/state combination. There are about 30 entities and all 50 states in total. The output file should list the entities across the top and the states down the side. Any help is greatly appreciated! INPUT FILE FORMAT Entity1 Entity1 Entity1 Entity2 Entity2 Entity2 Entity3 Entity3 Entity3 Alaska Arizona California Alaska Arizona California Alaska Arizona California Amount $500 $100 $600 $80 $100 $200 $80 $100 $200 Amount $100 $500 $80 $200 $600 $500 $200 $600 $500 Amount $80 $600 $500 $600 $100 $500 $600 $100 $500 OUTPUT FILE FORMAT Entity1 Entity2 Entity3 Alaska Sum of Alaska for Entity1 Sum of Alaska for Entity2 Sum of Alaska for Entity3 Arizona Sum of Arizona for Entity1 Sum of Arizona for Entity2 Sum of Arizona for Entity3 California Sum of California for Entity1 Sum of California for Entity2 Sum of California for Entity3

[Luke_C]

Hi @CindyA 

The sample data is a little tough to follow, but the summarize tool should be able to help you. Here's a sample based on what I think you're data is like:

[CindyA]

I was finally able to upload the file but also pasted it here.

Input File

	Entity1	Entity1	Entity1	Entity2	Entity2	Entity2	Entity3	Entity3	Entity3
	Alaska	Arizona	California	Alaska	Arizona	California	Alaska	Arizona	California
Amount	$  500	$   100	$      600	$    80	$   100	$      200	$        80	$       100	$       200
Amount	$  100	$   500	$        80	$  200	$   600	$      500	$      200	$       600	$       500
Amount	$    80	$   600	$      500	$  600	$   100	$      500	$   600	$       100	$       500

Output File

	Entity1	Entity2	Entity3
Alaska	Sum of Alaska for Entity1	Sum of Alaska for Entity2	Sum of Alaska for Entity3
Arizona	Sum of Arizona for Entity1	Sum of Arizona for Entity2	Sum of Arizona for Entity3
California	Sum of California for Entity1	Sum of California for Entity2	Sum of California for Entity3

[Luke_C · Accepted solution]

Hi @CindyA 

Thanks, the double headers make this tricky,

1. Use the sample tools to isolate the first set of headers (entities) from the rest of the data
2. Transpose each set of data and join together
3. Summarize and crosstab back

[Qiu]

@CindyA 
A bit different version for you.