Joining the Results of Boxes Checked on a Form Entry (2 Solutions)

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Edit: I had used numbers as my column names which in turn populated my first row. Another user made a good point that the number 1 in the first row will not actually duplicate my column name, but will read as "true" since it is a checkbox column.

 

The column names and formulas have been updated to account for this.

.

I think it's best explained with an example... We have 5 checkboxes that we will just call A through E for simplicity. User 1 selects A, C and E. User 2 selects D. User 3 selects A and B.

 

The sheet layout (column headers in bold):

User       A          B          C          D          E

               A           B          C          D          E

User 1     y                       y                       y

User 2                                          y

User 3     y           y

.

Here is how the basic JOIN(COLLECT would look using a ", " as the delimiter.

=JOIN(COLLECT(A$1:E$1, A@row:E@row, 1), ", ")

User 1: A, C, E

User 2: D

User 3: A, B

Nice and clean with no extra commas cluttering things up. Usually good enough. But then a coworker laid down a challenge. Replace the last comma (if any) with the word "and".

.

Well this pretty little mess accomplished that:

=SUBSTITUTE(JOIN(COLLECT(A$1:E$1, A@row:E@row, 1), ", "), ", ", " and ", COUNTIFS(A@row:E@row), 1) - 1)

 

User 1: A, C and E

User 2: D

User 3: A and B

.

Hope this helps anyone who happens to stumble upon this little thread...

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!