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

Consolidating large data set into summary table

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

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

Datasets

Accepted answers

afv2688

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

transpose_data1.yxmd

All comments

afv2688

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

transpose_data1.yxmd

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

20200130 - ConsolidatingTable.yxmd

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

solution.yxmd

Felix776

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703