Crosstab duplicate rows into as many columns as we need

[arthurmauk]

Hi, I have a dataset that looks like this:

ID	Name	State
1	John	Approved
1	Steve	Approved

I need to get it to something like this:

ID	Name1	State1	Name2	State2
1	John	Approved	Steve	Approved

I was attempting this using Crosstab but I couldn't make it work, is it a different tool I need or am I using it wrongly? Thanks in advance.

[atcodedog05 · Accepted solution]

Hi @arthurmauk 

This is how you can do it. You would need a group ID to get columns separate 

Workflow:

1. Using transpose convert columns to rows.

2. Using multi-row formula with groupby on ID, Name (column name) create group ID.

3. Using formula tool conact to column name

4. Using crosstab tool ID as ID, New column names as column name & value as value convert it to table.

Hope this helps 🙂

[arthurmauk]

Perfect, this is exactly what I wanted, thanks!

I now have a new problem where the Crosstab sorted all my columns into Name1, Name2, Name3, ... Name 36, State1, State 2... I would like to change the sort to Name1, State1, Name2, State2, etc. How would I go about this? Thanks in advance again!

[atcodedog05]

Hi @arthurmauk 

Sadly there is no easy way of doing it. Here is how you can do it.

Workflow:

When columns are crosstabed it is arranged in ascending order of column names. Hence i am creating an column using record ID which will be the column name by that the actual columns would be sorted in the required manner.

1. Sorting based on GroupID and Name. So that we get Name1, State1, Name 2, State 2...

2. I am using recordID and setting position. Since ID(pre-existing column) is not there in the name rows i am adding it using union tool.

3. Crosstabing it with name as recordID and value as new name. This way column name is 1,2,3.. (position holder) under that actual column names as 1st row data.

4. Using dynamic rename tool i am changing column name by taking names from 1st row. Now we have columns with position maintained.

5. While union tool the output data always maintains column order of the first connection. Hence column names order is first connection and data is second. This way you will get columns in desired order. 

Hope this helps 🙂