Hi, I have a dataset that looks like this: IDNameState1JohnApproved1SteveApproved I need to get it to something like this: IDName1State1Name2State21JohnApprovedSteveApproved 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.

Crosstab duplicate rows into as many columns as we need

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.

Help

Accepted answers

atcodedog05

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 🙂

names.yxmd

All comments

atcodedog05

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 🙂

names.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703