if-statementgoogle-sheetstransposearray-formulasgoogle-sheets-query
How to convert a data matrix (x's and nulls) into relational rows in Google Sheets?
I encountered a problem, where I need to convert a data matrix (with X's and empty cells) into some kind of a list; I have already seen similar questions in here, but I was unable to apply it for my data to work.
This is the matrix I have:
| A | B | ... | N |
-------------------------------
1 | x | | ... | x |
2 | | x | ... | |
. . . ... .
. . . ... .
. . . ... .
N | x | x | ... | |
The columns and rows number can vary.
I want the output table to be like that:
| column1 | column2 |
-------------------------------
1 A
1 N
2 B
. .
. .
. .
N A
N B
I think it is understandable :) Any help would be appreciated :)
Here is the real data I use: https://docs.google.com/spreadsheets/d/1SlYKMrZy5cyQ7sljTvmiwu6XkIDN_WILDhEM4cWyYnk/edit?usp=sharing
Solution
try:
=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(Sheet1!B3:EM<>"", "♠"&Sheet1!A3:A&"♦"&Sheet1!B2:2&"♦", ))
,,999^99)),,999^99), "♠")), "♦"))
- Can you store a variable inside a if-clause?
- How do I test for exceptions in if-statements in python?
- How to write lambda function with three conditions
- Matching multiple number rows and check corresponding dates within 90 days
- Sum all cells for which corresponding cell is not a formula and doesn't return #VALUE AND date occurs in the past
- If condition that matches if grep outputs at least one result even though the exit code is 2 due to some permission denied
- Why does `ifelse()` behave differently than `if (cond) {...} else {...}`?
- A Return inside and outside an If Statement
- How can I use "*ngIf else"?
- How to check repeated letters in a string c#
- How to create an 'empty if statement' in Python
- How to avoid if statement? for the compiler cannot optimize it to simd
- Test if it exists a node HXT
- python else statement causes a continuous loop
- Series of if vs else-if statements benchmarking
- Google Sheets - make a list of the names in column A where column E is greater than zero
- Condition evaluation process in Java
- Excel - Array Formula to skip empty cells
- Groups of compound conditions in Bash test
- How to use the IF statement to avoid the Weather API fetch result crashing once the user types wrong the city name?
- Comparing Text with a lot of If/else
- Count cells with two conditions
- New containing div after every 3 records
- Which one of the logical AND (`&&`) and logical OR (`||`) operators take precedence over the other?
- Macro to generate chained else ifs
- Java: break statement in "if else"
- Group of conditions in IF THEN statements in VBA
- Evaluate boolean environment variable in Python
- How to check whether a mysql select statement result has a value or not in php?
- Combining multiple OR conditions with AND in Excel formula