Forum Discussion

ElRafaVaz33's avatar
ElRafaVaz33
Copper Contributor
May 20, 2022
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 ...
excel
Formulas and Functions
office 365
  • mtarler's avatar
    mtarler
    May 20, 2022

    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's avatar
ElRafaVaz33
Copper Contributor
May 21, 2022
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.
mtarler's avatar
mtarler
Silver Contributor
May 21, 2022
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
  • farmerjenna's avatar
    farmerjenna
    Copper Contributor
    Mar 23, 2023

    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? 

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      May 01, 2023

      farmerjenna 

      =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.

       

       

    • mtarler's avatar
      mtarler
      Silver Contributor
      Mar 23, 2023

      farmerjenna Patrick2788 gave a clever solution above but w/r to the original formula you can tweak it like this:

      =LET(in,F1:F6,rep,G1,
     s,SEQUENCE(ROWS(in)*rep,1,0),
     INDEX(in,MOD(s-1,rep)+1))

      so basically instead of using QUOTIENT which will give the integer value after dividing the number of rep (i.e. 1,1,1,2,2,2,3,3,3) I changed it to use MOD which will give the remainder after dividing so it gives (0,1,2,0,1,2,0,1,2) and then +1 to get index 1,2,3... 

      another option using a variation on Patrick2788 's first answer is:

      =TOCOL(CHOOSE(SEQUENCE(1,6,1,0),A1:A3),,1)

      in this case that very last 1 can be a 0 to create a,a,a,b,b,b,c,c,c or a 1 to make it a,b,c,a,b,c,...

      btw the "6" is the number of repeates and the A1:A3 is the input range and this assumes a 'column' and if you have a row of input you probably have to swap the (1,6,1,0) to be (6,1,1,0)

      In Patrick's actual version he used CHOOSECOLS so that would just get swapped with CHOOSEROWS

      as I mentioned, lots of options 🙂

      • fsdesloge's avatar
        fsdesloge
        Copper Contributor
        Feb 09, 2024
        Beautiful - simple & easy to understand.

Resources