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!
Best Answers
-
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))
-
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!
-
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.
Answers
-
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))
-
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?
-
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.
-
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.
-
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!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!