Input:

Order No    Textbook    Grade       Time          No of times to be repeated
1234        Biology       6      16:30-17:30               2
1235        Physics       7      20:00-21:00               3

Desired Output:-

1234        Biology       6      16:30-17:30
1234        Biology       6      16:30-17:30
1235        Physics       7      20:00-21:00
1235        Physics       7      20:00-21:00
1235        Physics       7      20:00-21:00

CodePudding user response：

Give a try on below formula-

=INDEX(SPLIT(FLATTEN(SPLIT(JOIN("",INDEX(REPT(BYROW(A2:D3,LAMBDA(x,TEXTJOIN("|",0,x)))&"@",E2:E3))),"@")),"|"))

To make it dynamic spill array, use-

=INDEX(SPLIT(FLATTEN(SPLIT(JOIN("",INDEX(REPT(BYROW(A2:INDEX(D2:D,MATCH("zzz",D2:D)),LAMBDA(x,TEXTJOIN("|",0,x)))&"@",E2:INDEX(E2:E,MATCH(9^9,E2:E))))),"@")),"|"))

CodePudding user response：

Input:

Solution:

Use SEQUENCE to create a loop returning the current row for each iteration. The loop is accomplished through REDUCE:

=REDUCE(A1:D1,E2:INDEX(E:E,COUNTA(E:E)),
  LAMBDA(a,c,
    LAMBDA(row,
      {
        a;
        IF(c > 1,
          REDUCE(row,SEQUENCE(c-1),LAMBDA(a_,c_,{a_;row})),
          row
        )
      }
    )(OFFSET(c,0,-4,1,4))
  )
)

Output:

Advantage:

No more string manipulation and emojis