Concatenating Dropdown Responses with Exclusions in Final Column

Hi all,

I've been deliberating on this one for a while now and not getting anywhere.

I have 20 columns which are single select dropdown (with the possibility to free type also). As an example, one of the column's choices are:

Compliant

Missing ToB

ToB not initialled

Incorrect ToB template used

Incorrect ToB uploaded

N/A because ...

while another column's choices are:

Compliant

Missing BGC document

Expired BGC document (at time of onboarding)

Illegible BGC document

Incorrect BGC document

Insufficient details on BGC document

N/A because waived by Client

N/A because not requested via JD

N/A because ...

I have a final column in which I want to show all the chosen dropdown responses in the previous 20 columns, separated by "; ", BUT not include the responses that say "Compliant" or "N/A because ... [with whatever free type text has been entered]".

Due to the number of columns and the free type element, I really don't want to do embedded IF statements if I can avoid it.

I can join them all together, but cannot eliminate the 2+ dropdown responses that I don't want included.

I've tried the basic:

=JOIN([Column 1]@row:[Column 20]@row, "; ")

as well as other iterations that gave a completely incorrect result. The above just returns the values from every column, of course.

Any suggestions would be most welcome!

Find more posts tagged with

Formulas

Accepted answers

jmyzk_cloudsmart_jp

Hi @AEForrest

I would use the JOIN(COLLECT formula combinations. like this;

=JOIN(COLLECT([Column2]@row:[Column20]@row, [Column2]@row:[Column20]@row, <>"Compliant", [Column2]@row:[Column20]@row, NOT(CONTAINS("N/A because", @cell))), CHAR(10))

If you want unique responses.

=JOIN(DISTINCT(COLLECT([Column2]@row:[Column20]@row, [Column2]@row:[Column20]@row, <>"Compliant", [Column2]@row:[Column20]@row, NOT(CONTAINS("N/A because", @cell)))), CHAR(10))

If you want to make sure "N/A because" is at the top of the response, change the condition as follows;

=JOIN(COLLECT([Column2]@row:[Column20]@row, [Column2]@row:[Column20]@row, <>"Compliant", [Column2]@row:[Column20]@row, LEFT(@cell, 11) <> "N/A because"), CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=20fee9ab17554cbe9ebc14364132ae85

AEForrest

https://community.smartsheet.com/discussion/comment/414758#Comment_414758

Thanks so much for your help on this @jmyzk_cloudsmart_jp. With your suggestions, I've been able to build exactly the complex formula I needed.

To summarise, I have joined several single select dropdown columns (but excluded certain entries) and a multi-select dropdown column, with a semicolon delimiter, a space, and a carriage return.

=JOIN(DISTINCT(COLLECT([Column 1]@row:[Column 19]@row, [Column 1]@row:[Column 19]@row, <>"Compliant", [Column 1]@row:[Column 19]@row, NOT(CONTAINS("N/A because", @cell)))), (CHAR(59) + " " + CHAR(10))) + IF(JOIN(DISTINCT(COLLECT([Column 1]@row:[Column 19]@row, [Column 1]@row:[Column 19]@row, <>"Compliant", [Column 1]@row:[Column 19]@row, NOT(CONTAINS("N/A because", @cell)))), (CHAR(59) + " " + CHAR(10))) <> "", (CHAR(59) + " " + CHAR(10)), "") + SUBSTITUTE([Column 20]@row, CHAR(10), CHAR(59) + " " + CHAR(10))

The result:

Happy days!

jmyzk_cloudsmart_jp

Happy to help!😁 @AEForrest

It took some time to understand your formula.

I will leave a note as a reminder.

=JOIN(
	DISTINCT(
	COLLECT(
	[Column 1]@row:[Column 19]@row, 
	[Column 1]@row:[Column 19]@row, <>"Compliant", 
	[Column 1]@row:[Column 19]@row, NOT(CONTAINS("N/A because", @cell)))), 
	(CHAR(59) + " " + CHAR(10))) 
+ IF(
	JOIN(
	DISTINCT(
	COLLECT(
	[Column 1]@row:[Column 19]@row, 
	[Column 1]@row:[Column 19]@row, <>"Compliant", 
	[Column 1]@row:[Column 19]@row, NOT(CONTAINS("N/A because", @cell)))), 
	(CHAR(59) + " " + CHAR(10))) <> "", 
	(CHAR(59) + " " + CHAR(10)), "") 
+ SUBSTITUTE([Column 20]@row, CHAR(10), CHAR(59) + " " + CHAR(10))

Joined several single select dropdown columns (but excluded certain entries) .
A semicolon delimiter, a space, and a carriage return if 1. is not blank.
A multi-select dropdown column, with a semicolon delimiter, a space, and a carriage return.

I like the third part.

All comments

jmyzk_cloudsmart_jp

Hi @AEForrest

I would use the JOIN(COLLECT formula combinations. like this;

=JOIN(COLLECT([Column2]@row:[Column20]@row, [Column2]@row:[Column20]@row, <>"Compliant", [Column2]@row:[Column20]@row, NOT(CONTAINS("N/A because", @cell))), CHAR(10))

If you want unique responses.

=JOIN(DISTINCT(COLLECT([Column2]@row:[Column20]@row, [Column2]@row:[Column20]@row, <>"Compliant", [Column2]@row:[Column20]@row, NOT(CONTAINS("N/A because", @cell)))), CHAR(10))

If you want to make sure "N/A because" is at the top of the response, change the condition as follows;

=JOIN(COLLECT([Column2]@row:[Column20]@row, [Column2]@row:[Column20]@row, <>"Compliant", [Column2]@row:[Column20]@row, LEFT(@cell, 11) <> "N/A because"), CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=20fee9ab17554cbe9ebc14364132ae85

AEForrest

https://community.smartsheet.com/discussion/comment/414747#Comment_414747

Hi @jmyzk_cloudsmart_jp , thanks so much! I've used the unique version formula you have suggested. But it doesn't include the semi-colon delimiter I need. How can I incorporate this?

Also, I seem to have omitted one (apparently important!) detail in my original post.

All but one of the columns is a single dropdown, but one is a multi-dropdown (with free type possible). If I include this unusual column, the formula returns #INVALID DATA TYPE.

Any ideas how to get around this?

AEForrest

https://community.smartsheet.com/discussion/comment/414751#Comment_414751

Just to add: I looked at other posts but couldn't find the answer to getting two delimiters, but I've played around and just worked out how (I wanted semi-colon, then a space, then a carriage return) 🤗

CHAR(59)+" "+CHAR(10)

Output looks like this:

Posting this here in case anyone else finds it useful.

jmyzk_cloudsmart_jp

Hi @AEForrest

semi-colon delimiter

Please change CHAR(10) to "; ".

=JOIN(DISTINCT(COLLECT([Column2]@row:[Column20]@row, [Column2]@row:[Column20]@row, <>"Compliant", [Column2]@row:[Column20]@row, NOT(CONTAINS("N/A because", @cell)))), CHAR(10))

a multi-dropdown

The DISTINCT function is causing the error as the formula without the DISTINCT works, but it returns the joined words of multiple dropdowns. (Not A; B; C but A(new line)B ; C, if A and B are the multiple dropdown list items.)

Example

"Expired BGC document (at time of onboarding)

Incorrect ToB uploaded; Incorrect BGC document"

So, you need to extract each list of multiple dropdown lists. On the topic, I have recently posted a rather lengthy comment. (https://community.smartsheet.com/discussion/comment/414692#Comment_414692)

Since multiple dropdown list items are separated by CHR(10), you need to find the location of the CHAR(10) and use the text function like LEFT or MID to extract the list items.

https://community.smartsheet.com/discussion/comment/414692#Comment_414692

AEForrest

https://community.smartsheet.com/discussion/comment/414758#Comment_414758

Thanks so much for your help on this @jmyzk_cloudsmart_jp. With your suggestions, I've been able to build exactly the complex formula I needed.

To summarise, I have joined several single select dropdown columns (but excluded certain entries) and a multi-select dropdown column, with a semicolon delimiter, a space, and a carriage return.

The result:

Happy days!

jmyzk_cloudsmart_jp

Happy to help!😁 @AEForrest

It took some time to understand your formula.

I will leave a note as a reminder.

=JOIN(
	DISTINCT(
	COLLECT(
	[Column 1]@row:[Column 19]@row, 
	[Column 1]@row:[Column 19]@row, <>"Compliant", 
	[Column 1]@row:[Column 19]@row, NOT(CONTAINS("N/A because", @cell)))), 
	(CHAR(59) + " " + CHAR(10))) 
+ IF(
	JOIN(
	DISTINCT(
	COLLECT(
	[Column 1]@row:[Column 19]@row, 
	[Column 1]@row:[Column 19]@row, <>"Compliant", 
	[Column 1]@row:[Column 19]@row, NOT(CONTAINS("N/A because", @cell)))), 
	(CHAR(59) + " " + CHAR(10))) <> "", 
	(CHAR(59) + " " + CHAR(10)), "") 
+ SUBSTITUTE([Column 20]@row, CHAR(10), CHAR(59) + " " + CHAR(10))

Joined several single select dropdown columns (but excluded certain entries) .
A semicolon delimiter, a space, and a carriage return if 1. is not blank.
A multi-select dropdown column, with a semicolon delimiter, a space, and a carriage return.

I like the third part.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.