Is there an easy way to concatenate all combinations of non-null columns to 1 string?

[AkisM]

I have about 20 different columns, each one with a unique comment describing the record in that row (They are different data validation checks). To make it easier for the reviewer to see, I would like to go from 20 to 1. When there is a value in one of those columns, add them to the master column "Comment". Ignore OK's. Example below:

Value	Check1	Check2	Check3	Check4	Check5	Check6
40	OK	OK	bad format	check duplication	check formula	OK

would like it to be

Value	Comment
40	bad format and check duplication and check formula

The "before" table could have any number of different combinations of checks that are OK and checks that aren't ok and should be included in the comment.

Obviously with a larger number of checks the number of combinations of possible OK's or not OK is too big to code in a formula... Is there an easy way to achieve this?

[BenMoss]

Hi @AkisM 

This process is easily achievable with the transpose tool, filter tool and summerize tool.

I've attached an example for you.

Ben

[AkisM]

@BenMoss Thanks, is there a way to separate the concatenations with something like "and" or a comma to make it more legible? Keep in mind they're not always simple 2-word sentences.

[BenMoss · Accepted solution]

Yes in the summerize tool, when you select the field you are concatenating in the bottom panel you are able to specify the first, middle and last delimiters.

Ben

[jacob_kahn]

I think that @BenMoss 's response is the best option for you.

You can also use a transpose and cross tab tool. Embedded in the cross tab tool is the option to specify the delimiter.