Cross Tab/Transpose - Rearranging Data

[RWars]

I'm trying to figure out how to arrange a dataset I have. Based on the problem, I think that the transpose or cross tab tools (or possibly a combination of both) may provide the solution, but I'm not entirely sure. Here is a sample :

Name	Type	Province	Reports to	System
AIA	Office	BC	Abbot	ICS
AIA	Office		Surrey	ICES
AIA		BC	Pac	GCMS
Herd	Port	NL		ORA

I'm trying to rearrange the data so that any common entries in the 'Name' column will be collapsed into a single row. Whatever exists in the other columns for the common entries in the 'Name' field would be combined in the row under that column with some sort of delimiter. I'm thinking it would look something like this:

Name	Type	Province	Reports to	System
AIA	Office, Office,	BC, ,BC	Abbot,Surrey,Pac	ICS, ICES, GCMS
Herd	Port	NL		ORA

[Qiu · Accepted solution]

@RWars 
A Summarize Tool will do the trick.

[AngelosPachis]

Hi @RWars ,

You would need a summarise tool for that task. You can configure it as shown below:

EDIT : Not as fast as Qiu here

Hope that helps,

Angelos

[grazitti_sapna]

Hi @RWars , you can try both the ways either with summarize or with transpose and crosstab both ways are feasible.

If this works, kindly mark this post as solution.

Thanks.

[messi007]

@RWars,

Please try this 🙂

Hope that helps,

Regards,