With the help of the FILTER function, we can create a list from a single column checked TICK BOXES in Google Sheets.

When there are multiple columns with tick boxes (checkboxes), we can copy the formula for each column.

Is there any alternative solution that can handle multiple-column tickboxes all at once?

Yep! Even though using multiple FILTER formulas are the easiest way, we can write a dynamic formula to create a list from multiple-column checked Tick Boxes in Google Sheets.

Here is one example.

I have a list of vegetables (items) in the first column, and customers’ selection is in the subsequent three columns.

If so, how to create a customer-wise selected items list?

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

In F2, insert the following FILTER formula.

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

Copy it to G2 and H2.

The formula filters the vegetables in A3:A7 if checked (ticked) by the customer in B3:B7.

The range reference of the list of vegetables is absolute. It’s $A$3:$A$7, not mere A3:A7.

So when you copy the formula across, it won’t change the column reference.

But that is not the case with the tick box range B3:B7. It’s relative. So it changes the column when you copy the formula across.

The CURLY BRACKETS help to add the header of each tick box column to the top of the output.

The above is the easiest way to create a list from single/multiple columns checked Tix Boxes in Google Sheets.

The below solutions are for those who don’t want to copy the formula across.

There are two methods; one uses regular functions (old), and the other uses a Lambda solution (new).

I prefer the latter as it’s much simple compared to the former.

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

Here we will follow an unpivot, running count, and pivot max (string aggregation) approach.

The formula that creates a dynamic list from multiple columns checked Tick Boxes is not that complicated.

But if I give you the formula without any explanation, you can’t understand it correctly.

So let me adopt a step-by-step method for writing the code/formula for you.

There are a total of three steps.

You will get each piece of code in each step, and we will combine it.

1. Unpivot the List

Formula:

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

The above formula will create an UNPIVOT list of checked tick boxes.

We have used an IF logical statement, i.e., if(B3:D=true,A3:A7,) within FLATTEN to bring the unpivot effect to only checked tick boxes (TRUE values).

2. Running Count of TRUE Values

In this step, we should create a virtual (helper) column.

What will be the content in that column?

It contains the running count of customers in column range F2:F9.

We will combine that with the Step # 1 output as per the following syntax.

{running_count_of_true_values,unpivoted_list}

That is equal to {step_2,step_1}.

It’s easy to create a physical helper column of the Running Count of TRUE values aka Checked Tick Boxes.

The below formula will take care of that in cell E2.

Physical Helper Column

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

How to convert it to a virtual helper column?

Virtual Helper Column

The virtual helper column will make the formula that creates a dynamic list from checked Tick Boxes somewhat complex looking.

It’s because we will replace direct-range references with equivalent lengthy formulas.

We should replace the following references/formula parts.

1. row(F2:F9) – It appears twice in the formula.

The above formula returns the row numbers of the range F2:F9.

The number of rows in F2:F9 is equal to the count of checked tick boxes in B3:D7.

So we can replace the above formula with sequence(countif(B3:D7,true)).

You may please replace both occurrences of the formula parts.

2. F2:F9 – It also appears twice in the formula.

To get the values in F2:F9, we can extract the first column of the step # 1 formula result.

For that, just replace Select * with Select Col1 in the Step # 1 formula. Here is how.

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

Note:- You feel free to remove the ArrayFormula and the last closing bracket.

Here is the Step # 2 formula after the said modifications.

=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")))

Now let’s make it a virtual helper column.

For that combine the above formula with the Step # 1 formula as per the syntax {step_2_formula, step_1_formula}.

={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"))}

3. Pivot Max to Create a List from Multiple Column Checked Tick Boxes

In the below final step, we will use Step # 2 output as the ‘data’ in a QUERY formula.

In short, to create a dynamic list from multiple columns checked Tick Boxes, we will do three things.

1. Unpivot the source data (we will exclude unchecked checkboxes).

2. Add a virtual running count column to the unpivoted ‘data’.

3. Pivot the unpivoted ‘data’ back.

Now only the third step is pending. Here;

We will aggregate the strings (vegetables in column 3) in the unpivoted data using the Max function.

The aggregation will be based on the grouping of Column 1 values (running count).

We will pivot column 2 values which are the customers.

Syntax: Query(data,"Select Col1,max(Col3) group by Col1 pivot Col2")

Formula (should be entered 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")

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

Here is a modern-day formula using the BYCOL Lambda helper function.

You can replace the above complex formula in cell F2 with the below one.

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

Let me explain it to you step-by-step.

1. We can use =filter(A3:A7,B3:B7) or =filter(A3:A7,B3:B7=TRUE) to get the selected items by “Customer 1” (B3:B7).

2. We can use BYCOL to get all customers as follows =bycol(B3:D7,lambda(c, join("|",filter(A3:A7,c)))).

3. Transpose > Split > Transpose the step 2 output.

4. Add the header row (B2:D2) on the top with the help of Curly Braces.

That’s all about how to create a (dynamic) list from checked Tick Boxes in Google Sheets.

Thanks for the stay. Enjoy!

Example Sheet 11121

Resources

• Insert Check Box, Tick Mark in Google Sheets.
• Assign Values to Tick Box and Total It in Google Sheets.
• Change the Tick Box Color While Toggling in Google Sheets.
• How to Convert TRUE/FALSE to Checkboxes in Google Sheets.
• Check-Uncheck a Tick Box based on Value of Another Cell in Google Sheets.
• Tick Mark: Lock and Unlock Cells Using Checkboxes in Google Sheets.
• Highlighting Multiple Groups and Control Tick Boxes in Google Sheets.