Summarize a project in one row

[hellyars]

I have projects down and companies across.  The intersection is how a company segments a give project.  A company may segment the same project 1 or more ways.    

In the first table, there is more than 1 record for a given project if one or more companies want to segment it.

How can I summarize each project in one row?

Data In...

PROJECT	COMPANY_A	COMPANY_B	COMPANY_C	COMPANY_D	COMPANY_E	COMPANY_F
3041A	Tacos
4567X		Juice		X
4567X		Milk		X
4567X				X
9878C						Cars

Desired Outcome...

PROJECT	COMPANY_A	COMPANY_B	COMPANY_C	COMPANY_D	COMPANY_E	COMPANY_F
3041A	Tacos
4567X		Juice, Milk		X
9878C						Cars

[JagdeeshN]

Hi @hellyars ,

I think a summarize tool should do this for you.

Please find attached a sample workflow that showcases the same.

Please do let me know if this helps.

Best,

Jagdeesh Narayanan

[hellyars]

@JagdeeshN  I know.  That's how I go to this point.  This approach forgets to combine Milk with Juice.  

[hellyars]

Got it.  sum using regular concat + transpose + split to rows using "," + unique + sum using regular concat + cross tab.

[hellyars · Accepted solution]

Here is a solution...