excelexcel-formularepeatexcel-lambda

Repeat a range a number of times


I have a simple problem, but I am not quite able to figure out the answer

enter image description here

Given a range (in blue), and a number of times to be repeated (in orange), I need to create 2 spill arrays in vertical:

I have looked for a solution with REDUCE & VSTACK, but I do not mind if the functions are different.

I have tried, for example, with

=LET(a, SEQUENCE(F2), REDUCE("",B5:B7,LAMBDA(x,y,VSTACK(x,y))))

... but the range is not repeated.

What am I doing wrong?


Solution

  • For the first:

    =LET(r,FILTER(A:A,A:A<>""),cnt,B1,INDEX(r,MOD(SEQUENCE(COUNTA(r) *cnt,,0),COUNTA(r))+1))
    

    enter image description here

    for the second:

    =LET(r,FILTER(A:A,A:A<>""),cnt,B1,INDEX(r,SEQUENCE(COUNTA(r) *cnt,,1,1/cnt)))
    

    enter image description here

    Both in one:

    =LET(r,FILTER(A:A,A:A<>""),cnt,B1,INDEX(r,HSTACK(MOD(SEQUENCE(COUNTA(r) *cnt,,0),COUNTA(r))+1,SEQUENCE(COUNTA(r) *cnt,,1,1/cnt))))
    

    enter image description here