Repeat a range a number of times

Question

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?

Answer 1

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

Answer 2

If you know the range to repeat, I think this is simpler w/o the let function...but that's subjective.

=INDEX(range,MOD(SEQUENCE(COUNTA(range)*number_times,,0),COUNTA(range))+1)
=INDEX(range,SEQUENCE(COUNTA(range)*number_times,,1,1/number_times))

Just hstack these two formulas if you want one formula.

Answer 3

1. Create a recursive lambda function naming it _MultiVertical with the Name Manager in Formulas:

=LAMBDA(rng,acc,n,IF(n>1,_MultiVertical(rng,VSTACK(acc,rng),n-1),acc))

rng = the range to multiply
acc = accumulator for adding with every iteration an additional rng and passing through at the end the result
n = multiply n times vertically

For this recursive function you have to indicate the range to multiply twice. When the function calls itself the value of rng stays the same. It has to be passed through to add it to acc

2. To avoid to have to have two parameters which always will have the same value I created this wrapper function with the Name Manager calling it MultiVertical:

=LAMBDA(rng,n,_MultiVertical(rng,rng,n))

This function calls the recursive function and passes through the range once asrng parameter and once as acc parameter.