Merge Data based on Unique Column Headers

Hi All,

I am looking to merge values under unique column header names. In the dataset some column names are repeated so there is alot of redundancy and white space in the data. Please see attached dummy input and output files which hopefully describe what I am after.

Is there a way (or tool i can use) that can do this dynamically in Designer? i.e. without having to define the column header names in a formula?

Thank you very much for any help you can give.

Ben

Input.xlsx

Output.xlsx

Datasets

Custom Tools

Data Investigation

Accepted answers

Tyro_abc

Hi Ben

Check if the attached workflow is working as per your expectation, let me know if you need explanation of any step

MergeColumn.yxmd

All comments

Tyro_abc

Hi Ben

Check if the attached workflow is working as per your expectation, let me know if you need explanation of any step

MergeColumn.yxmd

echuong1

This should achieve what you're looking for.

I used a transpose to pivot the data so all of the columns were not rows. Alteryx doesn't allow more than one column with the same name, so I used a data cleansing to remove the numbers that were appended to the field names. This is represented by the Name column after the transpose. Once I had the data cleaned, I used a cross-tab to pivot the data back. There is a group by on Unique ID to get every ID on a separate row, the name field became the headers, and the value the values.

Hope this helps!

New Workflow1.yxmd

bwyeth

Thank you for your reply, much appriciated

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335