Search code examples
google-sheetstransposegoogle-sheets-formulaarray-formulasgoogle-sheets-query

Partial transpose of Sheet

I have a Google Sheet with this format:

+---------+---------+---------+------------+------------+------------+------------+--------+--------+
| Field_A | Field_B | Field_C | 24/09/2019 | 25/09/2019 | 26/09/2019 | 27/09/2019 | day... | day... |
+---------+---------+---------+------------+------------+------------+------------+--------+--------+
| ValX    | ValY    | ValZ    | Val1       | Val2       | Val3       | Val4       |        |        |
| ValW    | ValY    | ValZ    | Val5       | Val6       | Val7       | Val8       |        |        |
+---------+---------+---------+------------+------------+------------+------------+--------+--------+

First 3 columns are specific fields and all other columns are related to one specific day in a given (and static) range.

I need to convert the table in the following format:


+---------+---------+---------+------------+-----------+
| Field_A | Field_B | Field_C |    Date    | DateValue |
+---------+---------+---------+------------+-----------+
| ValX    | Valy    | Valz    | 24/09/2019 | Val1      |
| ValX    | Valy    | Valz    | 25/09/2019 | Val2      |
| ValX    | Valy    | Valz    | 26/09/2019 | Val3      |
| ...     |         |         |            |           |
+---------+---------+---------+------------+-----------+

Basically, the first 3 columns are gathered as-is, but the day-column in transposed (is even the correct term?) with 2 values:

  • The date
  • The value in the cell related to date

Is something that can be achieved with formula or do I need to create a bounded AppsScript?

Following a sample Sheet demo: https://docs.google.com/spreadsheets/d/1cprzD96i-4NQ8tieA_nwd8s43yKF-M8Kww4yWNfB6tg/edit#gid=505040170

  • In Sheet Start you can see the initial data and format, 3 static columns and one column for every da
  • In Sheet End you can see the output format I'm looking for, the same 3 static columns, but the date and cell value related to date are transposed as a row.

You can see the Formula I used, TRANSPOSE for every row, where I select the days for the IV column and one row at a time for the V row. For the 3 static columns, I replicated the Formula for every instance of the day related to that row. This is working but requires much manual work to set up every single TRANSPOSE. I'm wondering if there is a more automatic way of doing this (except for using AppsScript, in that case, I'm already planning on doing this if not other solutions are available)

Solution 
  • =ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(Start!D2:F<>""; "♦"&TRANSPOSE(QUERY(TRANSPOSE(Start!A2:C&"♠");;999^99))&
 TEXT(Start!D1:F1; "dd/mm/yyyy")&"♠"&Start!D2:F; ));;999^99));;999^99); "♦")); "♠")))

    0