Align Data in same row

Question

Hello Alteryx Community.

My problem is as follows. 
I have data (more columns than in my example), that is in a staggered format like this:

IDNameFood_IDFavorite FoodDescriptionSport_IDFavorite SportDescription1Tom1AppleRound   1 2BananaLong   1    1SoccerBall1    2Kung-FuFight1    3ChessStrategy2Mark1PizzaHot   2    1TennisBall2    2HockeyIce

I somehow need to move all the data into the top row of each person, so that the data essentially looks like this:

IDNameFood_IDFavorite FoodDescriptionSport_IDFavorite SportDescription1Tom1AppleRound1SoccerBall1 2BananaLong2Kung-FuFight1    3ChessStrategy2Mark1PizzaHot1TennisBall2    2HockeyIce

Any person could have X amount of favorite foods or favorite sports.

Any ideas are greatly appreciated!

Thank you!

AngelosPachis · Accepted Answer

Hi @scollier1993 ,

I've created a workflow that tackles this problem.

Essentially I transposed the data to bring them in a long form and then I used a multi-row formula to flag consecutive blank cells in each column. Then I have assigned a new row ID and cross-tab back into the original form.

Hopefully that works for you, let me know if there are any questions.

Regards,

Angelos

Christine_Assaad · Accepted Answer

Hi @scollier1993

This is another way of doing it. Hope it helps. Cheers!