Convert columns to rows and repeat row labels in google sheets

user_19172772 0
Viewed 46

I am trying to convert table 1 to the format in table 2. How can I do it in google sheets ? Thank you very much.

Table 1 -

enter image description here

Table 2 -

enter image description here

google-query-language flatten transpose google-sheets arrays
1 Answers

use:

=ARRAYFORMULA(QUERY(TO_TEXT(SPLIT(FLATTEN(
 A3:A&"×"&B2:E2&"×"&TEXT(B1:E1, "dd/mm")&"×"&B3:E), "×")), 
 "where Col4 is not null", ))

enter image description here

user_56326290
Related