Combining data from 3 fields into a new field separated by comma

[Rishi_HSBC]

I have data for employees working on different projects as in the below example where A,B,C,D,E,& F represents projects.

Employee ID	A	B	C	D	E	F
1	Yes	Yes
2			Yes	Yes
3	Yes			Yes		Yes
4				Yes
5					Yes

I want to add a new column in which all the projects assigned to a employee will be consolidated separated by a comma. Something like in the below table.

Employee ID	A	B	C	D	E	F	Employee Projects
1	Yes	Yes					A,B
2			Yes	Yes			C,D
3	Yes			Yes		Yes	A,D,F
4				Yes			D
5					Yes		E

Can someone please help on this.

TIA !

Rishi

[davidskaife · Accepted solution]

Hi @Rishi_HSBC 

Here you go, one way of doing it:

Transpose the data, then filter out any blank records. Using the Summarize tool concatenate the remaining records (grouping by Employee ID) and join the results back to the original input. Finally sort on Employee ID 

[Rishi_HSBC]

wow!!

That's what I wanted.

Thanks David :)

[flying008]

Hi, @Rishi_HSBC 

Another solution for you: