1

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.

1

Please use the following formula

=QUERY({{"Year";ArrayFormula(TRANSPOSE(SPLIT(JOIN(" ",REPT(B1:E1&" ",COUNTA(A3:A)))," ")))}, 
              query({A2:A,B2:C;A3:A,D3:E}, 
                     "where Col1 is not null",1)}, 
          "select Col2, Col1, Col3, Col4 order by Col1, Col2",1)

If you want to order by city and then by year use

=QUERY({{"Year";ArrayFormula(TRANSPOSE(SPLIT(JOIN(" ",REPT(B1:E1&" ",COUNTA(A3:A)))," ")))}, 
              query({A2:A,B2:C;A3:A,D3:E}, 
                     "where Col1 is not null",1)}, 
          "select Col2, Col1, Col3, Col4 order by Col2, Col1",1)

enter image description here

(The above formula is actually a modified version of this answer. Please refer to it for more info and how the formula works.)

5
  • Thanks! Is there a possibility to make this formula adaptive? Currently, it needs to be extended manually if there are new year columns added. Imagine I have 20 more columns to the right...
    – philmcole
    Jan 15 at 0:04
  • Sure. You just add groups like A3:A,F3:G;A3:A,H3:I;A3:A,J3:K and so on. You also need to adjust your REPT values to B1:K1. I could show you at your test sheet but it is view only. Jan 15 at 0:15
  • "Thanks!" Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Jan 15 at 0:16
  • Sure, but that doesn't scale and is error prone. I need a formula that doesn't need changing when I add new columns.
    – philmcole
    Jan 15 at 0:18
  • Fair enough. Glad I could help :) Jan 15 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.