I am writing a complex Google Sheets formula, which I think I can simplify using the Query function. The Query formula takes two inputs: 1) data, 2) query. The data consists of about 20 columns where the odd-numbered columns are categories and even-numbered columns are the values of that category. (Note that there are a fixed number of rows, but various columns have variable number of non-blank entries.)
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| 1st Category | Value B1 | 2nd Category | Value D1 |
| Value B2 | Value D2 | ||
| Value B3 | |||
My dream output is as follows:
| Categories | Values |
|---|---|
| 1st Category | Value B1 |
| 1st Category | Value B2 |
| 1st Category | Value B3 |
| 2nd Category | Value D1 |
| 2nd Category | Value D2 |
| ...etc. | ...etc. |
Thanks for any thoughts !
Note: Common Table Expressions can't seem to be used in the Query function.
Add'l note: I don't mind and partially expect that I will have to repeat the query for the transformation of Column A and Column B ten times to get all the data, which is fine. The simpler first-step question is how to do that.
Sample Google Sheets for reference/work: 