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

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

ConcatenateList.yxmd