Concatenating Dropdown Responses with Exclusions in Final Column

AEForrest
AEForrest ✭✭✭
in Formulas and Functions

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!

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    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
    image.png


    · Share on FacebookShare on Twitter
  • AEForrest
    AEForrest ✭✭✭
    Answer ✓
    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:

    image.png




    Happy days!

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/22/24 Answer ✓

    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))
    1. Joined several single select dropdown columns (but excluded certain entries) .
    2. A semicolon delimiter, a space, and a carriage return if 1. is not blank.
    3. A multi-select dropdown column, with a semicolon delimiter, a space, and a carriage return.

    I like the third part.

    · Share on FacebookShare on Twitter

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    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
    image.png


    · Share on FacebookShare on Twitter
  • AEForrest
    AEForrest ✭✭✭
    edited 01/22/24
    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?

    · Share on FacebookShare on Twitter
  • AEForrest
    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:

    image.png

    Posting this here in case anyone else finds it useful.

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    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


    · Share on FacebookShare on Twitter
  • AEForrest
    AEForrest ✭✭✭
    Answer ✓
    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:

    image.png




    Happy days!

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/22/24 Answer ✓

    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))
    1. Joined several single select dropdown columns (but excluded certain entries) .
    2. A semicolon delimiter, a space, and a carriage return if 1. is not blank.
    3. A multi-select dropdown column, with a semicolon delimiter, a space, and a carriage return.

    I like the third part.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!