Showing multiple true checkboxes as joined or concatenated text in another column

KateP721

I have a volunteer submission form which has about 10 columns that exist as just checkboxes on/off - each of these relate to a conference work area and volunteers often select multiple.

To easily view the data for a single person/row without tons of scrolling and looking up and down at column titles, I want to aggregate all their checked ("true" or "1") boxes into one cell, but instead of showing true/false, show the name of the column.

After extensive searching in the community, I was able to make some headway and got the first two columns to work as expected, but as soon as I add another column, the formula breaks. I feel as if I've copied/pasted this every which way I can.

For the row 5 volunteer, as an example, they've checked off Auctions and Awards...

THIS WORKS

=IF([Action Team: Association Business]5 = 1, [Action Team: Association Business]$1 + ", ") + IF([Action Team: Auctions]5 = 1, [Action Team: Auctions]$1 + ", ")

and produces the desired result: Auctions,

And if I check Association Business, it will add that before Auctions, as expected.

HOWEVER, as soon as I add the next column, the formula breaks.

THIS DOES NOT WORK:

=IF([Action Team: Association Business]5 = 1, [Action Team: Association Business]$1 + ", ") + IF([Action Team: Auctions]5 = 1, [Action Team: Auctions]$1 + ", ") + IF([Action Team: Awards]5 = 1, [Action Team: Awards]$1 + ", ")

 

ANY ADVICE? I attempted putting my IF statements in parentheses and that didn't help. Should I be using a JOIN or COLLECT function instead? I attempted that and using SUBSTITUTE but it was getting quite complex and my head about exploded :-)

Help please! Thank you!!

Andrée Starå

Hi Kate,

It sounds to me that you should be able to use the JOIN function.

Can you maybe share the sheet(s) or some screenshots? That would make it easier to help.

You could use helper columns and the JOIN in combination with the COLLECT function.

In my example, I use ABH for Column Helper (Action Team: Association Business), and so on.

=IF(ABH@row = 1; "ABH")     

The same version but with the below changes for your and others convenience.    

=IF(ABH@row = 1, "ABH")

The formula will only show the AND delimiter for the cells that are not empty.

I.e., Association Business, Awards and so on.

=JOIN(COLLECT(ABH@row:AWH@row; ABH@row:AWH@row; NOT(ISBLANK(@cell))); ", ")    

The same version but with the below changes for your and others convenience.   

=JOIN(COLLECT(ABH@row:AWH@row, ABH@row:AWH@row, NOT(ISBLANK(@cell))), ", ")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting