Transform data from one to one to one to many

I am trying to take some data and create a one to many relationship in Alteryx. I have attached some sample data to show what the input is and what I want to output to resemble.

Input:

Desired Output: Desired Output.png

Was able to somewhat perform this using Multi-row formula to sequence each ID, then a Crosstab tool was able to line up the data but only for the IDs. All other data points were left off.

Attempted to use a Transpose then concatenate using the Crosstab tool, but I am unable to parse the concatenated data once that is done. Is this possible to have the dataset transformed from one to one into one to many?

Test Data.xlsx

Regex

Output

Parse

Data Investigation

Accepted answers

TimN

The last one I sent has data in distinct columns without concatenation. If you have a version with concatenation you can look at the Text to columns tool in the Parse folder that can separate into columns

All comments

TimN

Maybe something like this...

FK.yxmd

PHaisten

I need any related data to the employee ID to be on the same row. This over a larger data set than what I provided, so a filter would not work and it still does not place the related data into one row.

TimN

If you remove the filter, is that the output you want?

TimN

oops so sheet2 is what you have (input) but you want it like sheet1?

TimN

Maybe I missing your point but this looks like it produces as one line per parent...

FK.yxmd

PHaisten

Output I am seeing is not all on the same row for each related user. In my output example I have the employee as one row and each dependent under that user is added on to the same row with new columns. Is this possible to perform in alteryx?

PHaisten

Sheet 1 is the data input. Sheet 2 is the desired output.

PHaisten

The second one is what I am able to get, but the format needs to be each value associated with it must be in its own column. The Desired Output is what I need the data to be in. Once data is concatenated, is there a way to text to columns the data into individual columns?

TimN

PHaisten

I was able to separate the data into separate columns using text to columns, just had to use the tool 6 times to separate all of my columns. Used a Select tool after that to order the data correctly. Thank you for your help!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703