Align Data in same row

[scollier1993]

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:

ID	Name	Food_ID	Favorite Food	Description	Sport_ID	Favorite Sport	Description
1	Tom	1	Apple	Round
1		2	Banana	Long
1					1	Soccer	Ball
1					2	Kung-Fu	Fight
1					3	Chess	Strategy
2	Mark	1	Pizza	Hot
2					1	Tennis	Ball
2					2	Hockey	Ice

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

ID	Name	Food_ID	Favorite Food	Description	Sport_ID	Favorite Sport	Description
1	Tom	1	Apple	Round	1	Soccer	Ball
1		2	Banana	Long	2	Kung-Fu	Fight
1					3	Chess	Strategy
2	Mark	1	Pizza	Hot	1	Tennis	Ball
2					2	Hockey	Ice

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

Any ideas are greatly appreciated!

Thank you!

[AngelosPachis · Accepted solution]

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 solution]

Hi @scollier1993 

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

[scollier1993]

Ingenious! Thank you!