Multiple values in rows need into 1 row multiple columns

[Nmassarone]

Hello, pretty new to Alteryx trying to figure something out...I have a situation where i have a column of account numbers that can be duplicated based on another column values. i need this moved into 1 column for account number and then each registration type in its own column. i tried a few things with the cross tab tool which almost gets what i want but every account number becomes its own column when really i would like everything summed up into 1 column.

Here's an example of the original table..

Account Number	Master ID	Registration
0001234	5678	IRA
0001234	4371	I
0001234	3243	403B
04654	9876	I
5474WBX	9657	I
5474WBX	4329	IRRL
654987	12897	IRA
654987	9856	403B
654987	8734	I
654987	17789	IRA
654987	17789	IRA
654987	68711	I

Right now when i use the Cross Tab Tool it comes out like this

Account Number	0001234	04654	5474WBX	654987
0001234	IRA, I, 403B
04654		I
5474WBX			I, IRRL
654987				IRA, 403b, I, IRA, IRA, I

Ideally if i could get it all summed up into 1 column like this

Account Number	Registration
0001234	IRA, I, 403B
04654	I
5474WBX	I, IRRL
654987	IRA, 403b, I, IRA, IRA, I

And then my final goal is to have any duplicate registrations inside the cell removed so it would only show unique values in the cell like this....

Account Number	Registration
0001234	IRA, I, 403B
04654	I
5474WBX	I, IRRL
654987	IRA, 403b, I

Any help on this would be appreciated thank you for taking the time to look at this.

[binuacs · Accepted solution]

@Nmassarone 

[atcodedog05 · Accepted solution]

Hi @Nmassarone 

My take on this.

Workflow:

Hope this helps : )

[Nmassarone]

Awesome both @binuacs and @atcodedog05  worked great i guess i was overthinking this thank you for the help and replies appreciate it.

[atcodedog05]

Happy to help : ) @Nmassarone 

Cheers and have a nice day!