google-sheetssyntaxgoogle-sheets-formulaunpivotgoogle-query-language
Unpivot data table with column groups
My data is in this format:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| USER1 | A-DATA1 | A-DATA2 | A-DATA3 | B-DATA1 | B-DATA2 | B-DATA3 |
| USER2 | A-DATA1 | A-DATA2 | A-DATA3 | B-DATA1 | B-DATA2 | B-DATA3 |
The desired results would look like this:
| A | B | C | D |
|---|---|---|---|
| USER1 | A-DATA1 | A-DATA2 | A-DATA3 |
| USER1 | B-DATA1 | B-DATA2 | B-DATA3 |
| USER2 | A-DATA1 | A-DATA2 | A-DATA3 |
| USER2 | B-DATA1 | B-DATA2 | B-DATA3 |
I test many things, with INDEX - ROW - COLUMN - TRANSPOSE but I never get the desired result... If you have a great idea, that would be super helpful.
Solution
try:
=QUERY({A3:D; A3:A\ E3:G}; "where Col1 is not null order by Col1"; )
