How can I get multiple vertical data to appear horizontally by unique ID number???

Hello,

I am trying to take my vertical data and have the "hobbies" that appear by unique ID number appear on one line horizontally instead. I tried the transpose option but can't seem to get the hobbies & suggested hobbies to only appear on one line. I also thought of adding a new column but can't figure out the formula to pump out "any value".

Any suggestions????

Hobbies appear vertical.PNG

Hobbies to appear hortizontally.PNG

Raw Data - Hobbies.xlsx

Desired outcome.xlsx

Developer Tools

Designer Cloud

Transformation

Accepted answers

Qiu

@starrmar
With my favorite Tile tool 😁

1121-starrmar.yxmd

Hammad_Rashid

You want to pivot your data from a vertical layout to a horizontal layout based on a unique ID number. If you have multiple rows with the same ID, and each row represents a different hobby or suggested hobby, you can use the "Cross Tab" tool in Alteryx Designer to achieve this.

Here are the general steps to perform this operation:

Sort Your Data:
- Before using the Cross Tab tool, it's a good idea to sort your data based on the unique ID and any other relevant fields.
Use Cross Tab Tool:
- Drag and drop the "Cross Tab" tool onto your workflow canvas.
- Connect it to the output of the data you want to pivot.
Configure Cross Tab:
- Configure the Cross Tab tool by selecting the unique ID column as the "Key Field."
- Select the column containing hobbies as the "Data Field."
- Optionally, you can set the "Prefix" and "Suffix" if you want to add labels to your columns.
Run the Workflow:
- Run the workflow to see the results.

The Cross Tab tool should create a new column for each unique hobby or suggested hobby, and the data for each unique ID will be pivoted horizontally.

All comments

Qiu

@starrmar
With my favorite Tile tool 😁

1121-starrmar.yxmd

Hammad_Rashid

Here are the general steps to perform this operation:

Sort Your Data:
- Before using the Cross Tab tool, it's a good idea to sort your data based on the unique ID and any other relevant fields.
Use Cross Tab Tool:
- Drag and drop the "Cross Tab" tool onto your workflow canvas.
- Connect it to the output of the data you want to pivot.
Configure Cross Tab:
- Configure the Cross Tab tool by selecting the unique ID column as the "Key Field."
- Select the column containing hobbies as the "Data Field."
- Optionally, you can set the "Prefix" and "Suffix" if you want to add labels to your columns.
Run the Workflow:
- Run the workflow to see the results.

The Cross Tab tool should create a new column for each unique hobby or suggested hobby, and the data for each unique ID will be pivoted horizontally.

starrmar

Thank you so much! This worked perfectly!

starrmar

Thank you! This worked and the formula saved me on another project. Appreciate you!

Qiu

@starrmar
Glad to be help

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703