Forum Discussion
Solved
Creating a dynamic repeating sequence of arrays
Hi Excel community, I'm trying to find a way to create a dynamic array of repeating cells. I was attempting a similar technique on Google Sheets where you can use a Rept and TextJoin to create a ...
ElRafaVaz33 There are lots of possibilities. Here is one:
=LET(in,F1:F6,rep,G1,s,SEQUENCE(ROWS(in)*rep,1,0),INDEX(in,QUOTIENT(s,rep)+1))
Sometimes the challenge is to know when to stop!
You could start with a basic kind of formula
= TOCOL(CHOOSECOLS(names, SEQUENCE(1,repeat,1,0)))but then, by the time you have used LET to remove the nesting and describe the intermediate steps, then wrapped the formula with a named LAMBDA to describe the operation and to list its arguments, you might finish up with
Repeatλ
= LET(
index, SEQUENCE(1, repeat, 1, 0),
block, CHOOSECOLS(names, index),
TOCOL(block, , order)
)so the worksheet formulae are
= Repeatλ(names, repeat)
= Repeatλ(names, repeat, 1)to give the results
respectively.
It all depends how much effort one is willing to commit to making things 'simple'!