if-statementgoogle-sheets-formulatransposeflattengoogle-query-language
Join column header with array n times
I am looking to join column headers with their respective arrays any number of times depending on if the cell is not empty and if it has a checkbox.
I have a formula close but I can't get it to repeat for the array with the respective values.
=JOIN(", ",ARRAYFORMULA(QUERY(TRANSPOSE($A$2:$3),"SELECT Col2 WHERE Col1 = FALSE AND Col2 IS NOT NULL")&"-"))&" : "&JOIN(", ",ARRAYFORMULA(QUERY(TRANSPOSE($A$2:$3),"SELECT Col2 WHERE Col1 = TRUE AND Col2 IS NOT NULL")&"-"))
This needs to be dynamic, meaning that if I add a new column header and values in column D it includes that set. Along with an infinite number of rows, however the rows sizes will always match. Any ideas would be appreciated!
Solution
use:
=ARRAYFORMULA(FLATTEN(QUERY(TRANSPOSE(REGEXREPLACE({
FLATTEN(QUERY(TRANSPOSE(FILTER(A3:C3&"-"&A4:C8, A2:C2=FALSE)&","),,9^9)),
IFERROR(SEQUENCE(ROWS(A4:C8))/0, ":"),
FLATTEN(QUERY(TRANSPOSE(FILTER(A3:C3&"-"&A4:C8, A2:C2=TRUE)&","),,9^9))},
",$", )),,9^9)))
- Conversion operator and bool operator for if init expression
- MATCH formula with multiple conditions returning true when false
- Mysql How to only select from a column if the column exists
- Unable to use IF statement in plpgsql
- Trouble Excluding Named Worksheets from a Loop
- Adding only some data labels and a total value at the top of my stacked bar chart
- Good C-coding style for multiple lines if conditions
- If statement to check if an array is not in another array
- Determining if a string contains a word
- How can I make a def function with my "if" statements?
- How to assign a variable in an IF condition, and then return it?
- Using or in if statement (Python)
- C# Creating a Square
- Return TRUE/FALSE column for checking if a set of columns contains a value/string
- Does Go have "if x in" construct similar to Python?
- Does if (myBool == true) differ computational wise to if(myBool)?
- Write a Basic Car rental program on java / problems
- Writing a "Chessboard" function in Python that prints out requested length of binary
- Will my program execute "else" even if it executed my "if"?
- Why Java does not see that Integers are equal?
- How to have the Xcode 3.1 compiler warn of assignment operator in an if statement?
- Nested if Statement good or bad?
- How to write if statement in .tmux.conf to set different options for different tmux versions?
- is not null Vs != null (Unity C#)
- VBA "does not equal" code not stepping through IF statement as expected
- Avoiding nested if else statement ladder in Python
- I want to take output from a function to use in a IF statement in another function
- What actually happens when we put conditions without [] (brackets) in if else block in a bash script?
- C++ The if statement is still true even when only one condition is met
- Fortran if statement errors