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

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

• the first one repeats the range the desired number of times
• the second one repeats each element of the range the desired number of times

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? any hints?

CodePudding user response：

For the first:

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

for the second:

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

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))))