How to use cross tab to transform data

[wuaw]

Hi team,

I have this set of data below:

The desired format is this:

I have using transpose and formula tool to try to manipulate the data. I think the next step is to use cross-tab, but I am not able to get the desired output format. Please suggest anything if you can think of a smarter solution than my current workflow. Thanks!

Screenshot of transpose tool:

Screenshot of formula tool:

[Qiu]

@wuaw 
I hope this works for you.

[danilang]

Hi @wuaw 

Here you go

Once your data is transposed, use a Regex Parse tool to separate the column header from the type.  You can also use a Formula tool with substring/findstring combinations instead of the regex.  After this you can use a Cross Tab.  This changes the output column order and replaces spaces in column names with underscores as shown in the top container.

To keep the underscores and column order add a Multirow tool to build the unique column numbers.  Cross Tab with the column numbers as headers, since the new columns get placed in numerical order.  Use a Dynamic Rename to change the numbered columns to the original column names.  the bottom Summarize build a list of unique column names and numbers that the Dynamic Rename uses to perform the substitution.

Dan