Search code examples
google-sheets

convert data from one row into multiple rows

I have an issue that needs to be resolved in Google Sheets. I need to convert data from one row into multiple rows as followsenter image description here

link file: https://docs.google.com/spreadsheets/d/1TJtThB-yhd-e_Hk6X_VrgIHSZchaHJM9EGhBNqXrqRU/edit?usp=sharing thanks in advance

Solution

  • Here's a possible solution

    =ARRAYFORMULA(
   LET(data,B3:G4,
       QUERY(
         REDUCE(TOCOL(,1),SEQUENCE(ROWS(data)),
           LAMBDA(acc,i,
             LET(cur_labels,CHOOSECOLS(INDEX(data,i),1,2),
                 cur_data,CHOOSECOLS(INDEX(data,i),SEQUENCE(COLUMNS(data)-2,1,3)),
                 VSTACK(acc,
                   REDUCE(
                     IF(SEQUENCE(COUNTIF(cur_data,"<>")),cur_labels),
                     SEQUENCE(COLUMNS(data)-2),
                     LAMBDA(acc_,i_,
                       IFNA(HSTACK(acc_,
                         VSTACK(
                           IFERROR(
                             IF(SEQUENCE(COUNTIF(IFERROR(CHOOSECOLS(cur_data,SEQUENCE(i_-1))),"<>")),),
                             TOCOL(,1)),
                           INDEX(cur_data,,i_)))))))))),
         "where Col2 is not null")))

    enter image description here

    For each row of the input array:

    • The first 2 columns are created by stacking on top of each other the first 2 columns of the input array an amount of times equal to the amount of non-empty values in the last 4 columns.
    • The last 4 columns are created by stacking empty values on top of each "x" where the amount of empty values is calculated as COUNTIF(previous_columns,"<>")-1 where previous_columns is the array of columns prior to the current one.