1

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
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Anusha Narayanam
  • 21
  • 2
  • Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! See [tag info page](https://stackoverflow.com/tags/google-sheets-formula/info) for official documentation, free resources and more details. – TheMaster Sep 28 '22 at 10:55
  • Reopened. The duplicate probably needs reversal. Rationale: The strategy mentioned in the [linked duplicate](https://stackoverflow.com/questions/70160891/how-to-repeat-a-data-set-several-columns-x-times-in-google-sheets) is similar to the strategy in [this answer](https://stackoverflow.com/a/73880367). There is another answer(mine) providing a different strategy altogether. It is better if this question is made canonical rather than the older one. – TheMaster Sep 30 '22 at 09:40

2 Answers2

1

Give a try on below formula-

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

enter image description here

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))))),"@")),"|"))

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • 1
    `INDEX`, and `MATCH(9^9` seems to solve alot of problems. – Osm Sep 28 '22 at 11:23
  • 1
    For dynamic row reference in a column, it works like magic. – Harun24hr Sep 28 '22 at 11:27
  • 1
    @Osm Always recommended [`INDEX/COUNTA`](https://stackoverflow.com/a/46884012/) instead of `IF(A:A="",,)` for ages. – TheMaster Sep 28 '22 at 12:00
  • @TheMaster `COUNTA` will work good when data in a column is continuous. In case of blank rows in a column then have data after few rows, use of match will detect last row. – Harun24hr Sep 28 '22 at 12:06
  • 1
    @Harun24hr I am aware of that and did mention it in my linked answer. That is the minor disadvantage. Advantage is you don't need to know the type of data -number or text, whereas with `MATCH`, you need to. I also believe `COUNT`(numeric operations in general) operation is faster than `MATCH`. – TheMaster Sep 28 '22 at 12:11
  • @TheMaster I am totally agree with you. – Harun24hr Sep 28 '22 at 12:12
1

Input:

Order No Textbook Grade Time
1234 Biology 6 16:30-17:30 3
1235 Physics 7 20:00-21:00 1

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:

Order No Textbook Grade
1234 Biology 6 16:30-17:30
1234 Biology 6 16:30-17:30
1234 Biology 6 16:30-17:30
1235 Physics 7 20:00-21:00

Advantage:

No more string manipulation and emojis

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • `REDUCE(A1:D1,` can it be replaced with `SEQUENCE(1,ROWS(A1:S1)...)`? I'll test it after few moments – Osm Sep 28 '22 at 14:10
  • 1
    @Osm No. Those are headers: `Order No Textbook Grade`. If you replace them, then they will be numbers. – TheMaster Sep 28 '22 at 14:12