CODEWITHSUNDEEP
google-sheetstransposegoogle-sheets-formulaarray-formulasgoogle-sheets-query
Deviling Master
Deviling Master

Reputation: 3113

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:

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

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)

Upvotes: 1

Views: 380

Answers (1)

player0
player0

Reputation: 1

=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

Upvotes: 2

Related Questions