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