0

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:

  • 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?

ZygD
  • 22,092
  • 39
  • 79
  • 102
vsoler
  • 1,027
  • 2
  • 8
  • 17

2 Answers2

4

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • There seems to be a small problem with your formulas. When I increase the namber of elements in the range from 3 to 4, the formulas are no longer valid. Could you please check if there is a problem? – vsoler Nov 07 '22 at 23:07
  • I edited the formulas many many times, make sure you are grabbing the current. You may need to refresh the page to see it. – Scott Craner Nov 07 '22 at 23:10
  • 1
    you're right, when I take your latest version, it works fine, thank you – vsoler Nov 07 '22 at 23:14
  • This is great and just what I needed! I've just got 365 after being stuck on 2016 for years so playing catch-up with some of the new functionality. I took the liberty of creating a lambda function out of your solution that I call Rept_Range; ```=LAMBDA(range,number_times,[grouped_stack],LET(filtered,FILTER(range,range<>""),IF(OR(ISOMITTED(grouped_stack),NOT(grouped_stack)),INDEX(filtered,MOD(SEQUENCE(COUNTA(filtered) *number_times,,0),COUNTA(filtered))+1),INDEX(filtered,SEQUENCE(COUNTA(filtered)*number_times,,1,1/number_times)))))``` – rkr87 Jan 26 '23 at 09:32
  • I simplified the above lambda function; ```=LAMBDA(range,number_times,[grouped_stack],LET(r,ROWS(range),g,MAX(grouped_stack,0),s,SEQUENCE(r*number_times,,g,MAX(g/number_times,NOT(g))),INDEX(range,IF(g,s,MOD(s,r)+1))))``` – rkr87 Jan 27 '23 at 10:44
-1

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.

toyota Supra
  • 3,181
  • 4
  • 15
  • 19
user3489967
  • 1
  • 1