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))
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))
This is perfect, thank you! I'd never heard of the LET function, but reading on it, it looks super powerful.
For anyone reading this after me, here the source list is given from F1:F6 while G1 holds the number of repetitions.
For anyone reading this after me, here the source list is given from F1:F6 while G1 holds the number of repetitions.
- YES, LET() is SUPER awesome addition. If you don't know about it definitely learn. Also look into FILTER, SORT, UNIQUE, and then LAMBDA (which gets a little more complicated but even more powerful). Some great things happening in Excelverse
mtarler I've been trying to wrap my head around the LET function for this application. What if the OP wanted the list to repeat in an ABCABCABC fashion instead of AAABBBCCC?
=TOCOL(MAKEARRAY(ROWS(A1:A3),6,LAMBDA(r,c,INDEX(A1:A3,r))),,1)Another alternative could be LAMBDA with MAKEARRAY. Range A1:A3 has values A, B and C in this example. 6 is the number of repeats in this example.
