How to Create a List from Multiple Column Checked Tick Boxes in Google Sheets

GiGiKing·March 14, 2024·0 comments

Imagine having a list of selected items from multiple column tick boxes in Google Sheets without the hassle of manually copying formulas for each column. Well, I’m here to let you in on a little secret that will make your life easier!

The Easiest Way to Create a List

With the help of the FILTER function in Google Sheets, you can easily create a list from a single column of checked tick boxes. But what if you have multiple columns? Don’t worry, I’ve got you covered!

Instead of using multiple FILTER formulas, you can write a dynamic formula that will handle multiple-column tick boxes all at once. Let me show you how.

List from Multiple Column Checked Tick Boxes - Example

Create a List from Checked Tick Boxes in Google Sheets (Non-Dynamic)

To get started, insert the following FILTER formula in cell F2:

={B2;filter($A$3:$A$7,B3:B7=true)}

Copy this formula to cells G2 and H2, and watch the magic happen! This formula filters the vegetables in column A if they are checked (ticked) by the customers in columns B and C. The range reference of the list of vegetables is absolute (i.e., $A$3:$A$7), so it won’t change when you copy the formula across. However, the tick box range B3:B7 is relative, so it will change its column reference when you copy the formula.

Create a Dynamic List from Multiple Column Checked Tick Boxes – Old Method

If you don’t want to copy formulas across, there are alternative methods. One such method uses regular functions, while the other uses a Lambda solution. Personally, I find the latter much simpler.

1. Unpivot the List

To unpivot the list, use the following formula in cell E2:

=ArrayFormula(query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select * where Col2 is not null"))

This formula will create an unpivoted list of checked tick boxes, excluding unchecked checkboxes. It uses an IF logical statement within FLATTEN to bring the unpivot effect to only the checked tick boxes (TRUE values).

2. Running Count of TRUE Values

In this step, we will create a virtual (helper) column that contains the running count of customers in column range F2:F9. Use the following formula in cell D2 to create the physical helper column:

=ArrayFormula(countifs(row(F2:F9),"<="&row(F2:F9),F2:F9,F2:F9))

To convert it to a virtual helper column, replace the direct-range references with the following formulas:

  • Replace row(F2:F9) with sequence(countif(B3:D7,true))
  • Replace F2:F9 with query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null")

Combine the formulas from step 2 and step 1 as follows:

={ArrayFormula(countifs(sequence(countif(B3:D7,true)),"<="&sequence(countif(B3:D7,true)),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"))),ArrayFormula(query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select * where Col2 is not null"))}

This will create a virtual helper column that combines the running count of TRUE values and the unpivoted list.

3. Pivot Max to Create a List

In the final step, we will use the output from step 2 as the ‘data’ in a QUERY formula to create the list. Enter the following formula in cell F2:

=query({ArrayFormula(countifs(sequence(countif(B3:D7,true)),"<="&sequence(countif(B3:D7,true)),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"),query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select Col1 where Col2 is not null"))),ArrayFormula(query(split(flatten(B2:D2&"|"&if(B3:D=true,A3:A7,)),"|"),"Select * where Col2 is not null"))},"Select Col1,max(Col3) group by Col1 pivot Col2")

This formula will aggregate the strings (vegetables) in the unpivoted data using the Max function, based on the grouping of column 1 values (running count). It will pivot column 2 values, which are the customers.

Create a Dynamic List from Multiple Column Checked Tick Boxes – New Method

If you prefer a more modern formula, you can use the BYCOL Lambda helper function. Replace the complex formula in cell F2 with the following formula:

={B2:D2;ArrayFormula(ifna(transpose(split(transpose(bycol(B3:D7,lambda(c, join("|",filter(A3:A7,c))))),"|"))))}

Let me break it down for you:

  1. Use =filter(A3:A7,B3:B7) or =filter(A3:A7,B3:B7=TRUE) to get the selected items by “Customer 1” (B3:B7).
  2. Use BYCOL to get all customers: =bycol(B3:D7,lambda(c, join("|",filter(A3:A7,c)))).
  3. Transpose > Split > Transpose the output from step 2.
  4. Add the header row (B2:D2) on top using Curly Braces.

That’s it! You now know how to create a dynamic list from checked tick boxes in Google Sheets. Enjoy the simplicity and convenience of this powerful feature!

To delve deeper into Google Sheets and discover more exciting possibilities, check out Crawlan.com for valuable resources and insights. Happy spreadsheeting!

Example Sheet 11121

GiGiKing

Related posts