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

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

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, [email protected]:[email protected], 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, [email protected]:[email protected], 1), ", "), ", ", " and ", COUNTIFS([email protected]:[email protected]), 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

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Paul,

    Excellent work! yes

    Best,

    Andrée

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Thanks Andree! Never really had need for the second option. I just had someone ask if it was possible. Hahaha.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Haha! You're welcome!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • I like the potential of this workaround but I'm having issues with the formula. See screen shots. It's not displaying the column header that was checked. Just true,true,true. No answer from the column headers. And then oddly the second one is saying true,false. Any ideas what's not working?

    Thank you for your help!

     

    Screenshot 2019-06-28 19.50.54.png

    Screenshot 2019-06-28 19.57.07.png

    Screenshot 2019-06-28 19.59.13.png

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi, 

    It seems like you missed adding the top row that should be the same as the column names.

    Did it work when you added that row?

    Hope that helps!

    Have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Thank you. That worked. However, there seems to be a glitch with the number 1 begin accepted in the checkbox field. 2-5 worked fine.

    I ended up just using the content that I wanted in my actual sheet and it worked fine! Thank you so much!

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Excellent!

    Happy to help!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I am actually glad you brought this up. I never use just plain numbers when I have this set up for a practical application, so I hadn't even considered the 1 giving a "true" value when repeating the column headers in the first row.

     

    Thanks for bringing this to my attention. I am going to go ahead an edit my post for clarity based on this.

     

    yesyes

Sign In or Register to comment.