I have data with the structure in the attached file. How can I make it look like the second file as concisely as possible? Whenever I try to use Cross Tab Tool it tries to sum or concatenate.
Attachments:
Answer:
The most efficient way to accomplish this is with a combination of Multi-Row Formula and a Cross Tab tool.
Using the formula in the Multi-Row Formula tool, you will get a unique record ID for each city. Then you can use the crosstab tool to group on City and Record ID while using the Metric field for your column headers and the Value field for your values. Finally Use concatenate as the method for aggregating.
The output should match the desired output you posted above.
Hope that helps!
Attachment:
