Concatenate columns shortcut

[akasubi]

I would like to concatenate the values of my columns into a single column, but there are 17 columns in total. Is there a shortcut to do this without having to manually type out the formula for each column name?

Additionally, I'd like to add a space in between each column value and where there is a blank value to ignore that column.

Is this possible?

Example data below

ID					Concatenated field
1	MILK				MILK
2		EGGS			EGGS
3		EGGS		BREAD	EGGS BREAD
4	MILK		CEREAL		MILK CEREAL
5	MILK	EGGS		BREAD	MILK EGGS BREAD

[estherb47 · Accepted solution]

Hi @akasubi 

With a little transpose, formula, summarize, it's easy to work with a ton of columns!

First, transpose the data with the key field of ID. That makes everything vertical. Then, use a formula tool to remove all of the spaces (Replace([Value]," ",""). I find that faster than the Data Cleansing tool.

Finally, summarize back together. ID is the group by field, and concatenate the values, using a space as the separator.

Let me know if that helps. Cheers! Esther

[TomWelgemoed]

Hi,

Yes, Pivot the columns first and then use the Concatenate option in the Summarise tool. Workflow example attached.

Regards,

Tom