Consolidating large data set into summary table

[Felix776]

Hi Alteryx Community!

I have a table which I like to summarized. 

from:

AAAAA	HDI3DL
AAAAA	SDF35
AAAAA	KG3FK
BBBBB	D75END
BBBBB	FH2JC
BBBBB	HD5I3D
BBBBB	SDF36
CCCCC	KG3FFK
CCCCC	D75N3D
DDDDD	KGFFK
DDDDD	D75ND
DDDDD	FHRJC
DDDDD	HDI3D
DDDDD	SDF38

to 

Model	Counts	Option 1	Option 2	Option 3	Option 4	Option 5
AAAAA	3	HDI3DL	SDF35	KG3FK
BBBBB	4	D75END	FH2JC	HD5I3D	SDF36
CCCCC	2	KG3FFK	D75N3D
DDDDD	5	KGFFK	D75ND	FHRJC	HDI3D	SDF38

I have been trying an only able achieve a concatenated version, I know a way to split them using "Text to Column" but if I have 100s of Models and "Text to Column" seems only able to split one column at a time PLUS have to specific the number columns without auto detection of how many deliminator there are.

Any help would be grateful! 

Many Thanks

[afv2688 · Accepted solution]

Hello @Felix776,

This will do it:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[Jonathan-Sherman]

Hi @Felix776,

You could use this method:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[danrh]

Two more options. The first is the most concise, but it relies on you manually putting in the max number of options in the Text-to-Column tool. If it's never going to be more than 5 options, great. If that could change, the second option is more dynamic and should be able to cope with however many options you throw at it. Take a look, see if either of these fit your needs.

Hope it helps!

Dan

[Felix776]

Thank you afv2688 ! I can't believe it is that simple! 

[afv2688]

Glad it helped!

If you need anything else let us know!

Regards

[Jonathan-Sherman]

If you need your count column @Felix776 then summarise to group by model, count model and join the summarise back on the model field