Concatenate column values using commas excluding black

[KketanPaliwal30]

Hi,

C1	C2	C3	C4
	B		C

I need the below output from the above table. 

C1	C2	C3	C4	Output
	B		C	B,C

Note: If C1 has value "a" then the output should be "abc"

[JagdeeshN]

Hi @KketanPaliwal30 ,

If the number of columns is constant, i.e they remain C1, C2, C3 and C4 you can just use a formula tool:-

[Output]=[C1]+','+[C2]+','+[C3]+','+[C4]

Hope this helps.

Best,

Jagdeesh

[JagdeeshN]

@KketanPaliwal30 ,

I just realized the above formula would add ',' to the string even if it is null.

Please find attached an alternative solution that takes care of that.

Best,

Jagdeesh

[Luke_C]

Hi @KketanPaliwal30 

Here's a solution that will work for any number of columns:

1. Add record ID for tracking
2. Transpose data
3. Filter out empty cells
4. Concatenate remaining
5. Join back