How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Tuesday

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

Posted by   on

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?


List from Multiple Column Checked Tick Boxes - Example
image # 1

Create a List from Checked Tick Boxes in Google Sheets


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 helps 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 tips are for those who don’t want to copy the formula across.


How to Create a Dynamic List from Multiple Column Checked Tick Boxes?


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


Step 1 - Unpivoted List
image # 2

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 to 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))

Step 2 - Running Count (Physical Helper Column)
image # 3

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


Step 2.1 - Running Count (Virtual Helper Column)
image # 4

="),"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("),"Select * where Col2 is not null")),"Select Col1,max(Col3) group by Col1 pivot Col2")

Dynamic List from Multiple Column Checked Tick Boxes - Example
image # 5

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

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!