I have a Google sheet in which the rows are unique cities and the columns are years. It can grow in two dimensions.
| | 2010 | 2011 |
-----------------------------------------------------------
| City | Temp low | Temp high | Temp low | Temp high |
-----------------------------------------------------------
| London | 10 | 11 | 21 | 22 |
| Birmingham | 12 | 13 | 23 | 24 |
However, this double header doesn't work for creating a pivot table.
How can I transform the table such that it grows only in the row dimension and the years are simply a value in a single column?
| City | Year | Temp low | Temp high |
-------------------------------------------
| London | 2010 | 10 | 11 |
| Birmingham | 2010 | 12 | 13 |
| London | 2011 | 21 | 22 |
| Birmingham | 2011 | 23 | 24 |
Here is a sample sheet of the above example.
The formula shouldn't need to be adapted when new rows or columns are added. However, the column pattern will remain the same and can be relied on.
Best Answer
Please use the following formula
If you want to order by city and then by year use
(The above formula is actually a modified version of this answer. Please refer to it for more info and how the formula works.)