if-statementgoogle-sheetstransposearray-formulasgoogle-sheets-query
Transform matrix into column on google sheets
I have a number of matrixes such as:
I would like to transform this data in such way, that it is resembled in one column, such as:
Here is the link to a testsheet with exactly this data: https://docs.google.com/spreadsheets/d/1kn6yYL3dsTZ7IL5Z8a5j0itfwZK9TkRQsBCzXdCG2zo/edit#gid=0
What I have done so far: I know, that I can do this using "transpose" or "mtrans" (same thing). But unfortunately for this I will have to do it manually. I'm looking for just ONE formular to solve this problem, so I don't have to always do it manually for every row.
Solution
use:
=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(MONATLICH!R3:Y<>""; "♠"&MONTH(MONATLICH!B3:B)&"♦"&MONATLICH!B3:B&"♦"&
MONATLICH!C3:C&"♦"&MONATLICH!R2:Y2&"♦"&MONATLICH!R3:Y&"♦"&MONATLICH!Z3:AG; ))
;;999^99));;999^99); "♠")); "♦"))
spreadsheet demo
- is not null Vs != null (Unity C#)
- 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
- How can I change certain values in a column based on multiple conditions, leaving others as they are if the conditions aren't met?
- If statements to process a specific numeric value of a variable always fall into the last if statement no matter the value of the variable
- If formula (the sum is less than 30 then roundup tp 30)
- In what conditions is one comparison for "if" and "if-else" at С as at assembly?
- Replace IFs and FOR LOOPs with STREAMs and LAMBDAs
- Symbol equivalent to NEQ, LSS, GTR, etc. in Windows batch files
- if statements matching multiple values
- How do I check if a string contains only numbers and not letters
- How to make a SQL "IF-THEN-ELSE" statement
- Using Javascript inside blade @if
- JavaScript conditional - how to test if a variable matches any of a list of values?
- powershell activity if static string is on substring on file
- Print largest & second largest number from three variables entered by the user by conditional statement
- php greater than certain time
- Text rpg that lets you move between rooms ignore all if statements testing for location
- extern variable prototype: comparison of array not equal to a null pointer is always true
- Calculate a score depending on multiple conditions in R
- Multiples of 3 or 5 Can this be done using else if statements?
- Using if_else in a mutate command yields an error about false having the wrong size
- Attempting to write a .bat script for reinstalling printers
- What is a good practice to check if an environment variable exists or not?
- Excel - IF formula TRUE statement output changing based on FALSE argument? (with DROP and VSTACK output)
- Why does comparing dates (using 'IF' function) return unexpected outcome?
- Can an Python writeline() be used in an IF statement?