Convert columns to rows and repeat row labels in google sheets-CodePudding

Home > Mobile > Convert columns to rows and repeat row labels in google sheets

Convert columns to rows and repeat row labels in google sheets

Time：08-10

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

CodePudding user response：

use:

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