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

Paul Newcome

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

Andrée Starå

Paul,

Excellent work! 

Best,

Andrée

Paul Newcome

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

Andrée Starå

Haha! You're welcome!

katfooddfw

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!

 

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

katfooddfw

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å

Excellent!

Happy to help!

Paul 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.