'Transforming data in spreadsheet (Excel or Google Sheets) such that every N rows gets autofilled
Lets say I have a spreadsheet as follows:
| name | x1 | x2 | x3 |
|---|---|---|---|
| a | 4 | 8 | 9 |
| b | 5 | 2 | 6 |
| c | 7 | 3 | 1 |
And I want it in the format
| name | var | value |
|---|---|---|
| a | x1 | 4 |
| a | x2 | 8 |
| a | x3 | 9 |
| b | x1 | 5 |
| b | x2 | 2 |
| b | x3 | 6 |
| c | x1 | 7 |
| c | x2 | 3 |
| c | x3 | 1 |
What is the best way to accomplish this in Google Sheets? Or am I better off just transforming the data in Python/R?
EDIT: Thanks everyone for the great solutions in spreadsheets. I found it simpler to just convert using Python, but I appreciate the newfound spreadsheet knowledge!
Solution 1:[1]
use:
=INDEX(QUERY(SPLIT(FLATTEN(A2:A&"×"&B1:D1&"×"&B2:D); "×"); "where Col3 is not null"))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | player0 |