SOLVED

Creating a dynamic repeating sequence of arrays

%3CLINGO-SUB%20id%3D%22lingo-sub-3407457%22%20slang%3D%22en-US%22%3ECreating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3407457%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excel%20community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20find%20a%20way%20to%20create%20a%20dynamic%20array%20of%20repeating%20cells.%20I%20was%20attempting%20a%20similar%20technique%20on%20Google%20Sheets%20where%20you%20can%20use%20a%20Rept%20and%20TextJoin%20to%20create%20a%20string%20of%20the%20right%20number%20of%20elements%2C%20and%20then%20a%20Split%20function%20to%20separate%20them%20into%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20I%20haven't%20been%20able%20to%20find%20a%20way%20to%20do%20this%20in%20Excel%3B%20I%20get%20stumped%20on%20the%20split%20step.%20Would%20anyone%20have%20any%20suggestions%20on%20how%20to%20go%20about%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20visual%20example%20of%20what%20I'm%20trying%20to%20achieve%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Screen%20Shot%202022-05-20%20at%2011.54.51%20AM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373782i00EB11379F1E2B42%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202022-05-20%20at%2011.54.51%20AM.png%22%20alt%3D%22Screen%20Shot%202022-05-20%20at%2011.54.51%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFor%20reference%20I'm%20using%20Office%20365%20on%20a%20mac.%20Perhaps%20I%20am%20thinking%20about%20this%20problem%20in%20the%20wrong%20way.%20Any%20direction%20I%20could%20investigate%20would%20be%20really%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3407457%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3407726%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3407726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1394037%22%20target%3D%22_blank%22%3E%40ElRafaVaz33%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20names_array()%0A%0ADim%20i%20As%20Integer%0ADim%20lngzeilemax%20As%20Integer%0ADim%20z%20As%20Integer%0ADim%20y%20As%20Integer%0A%0AWith%20Tabelle1%0A%0Alngzeilemax%20%3D%20.Range(%22B%22%20%26amp%3B%20.Rows.Count).End(xlUp).Row%0A%0AFor%20i%20%3D%203%20To%20lngzeilemax%20*%20Cells(1%2C%202).Value%0A%0AFor%20z%20%3D%201%20To%20Cells(1%2C%202).Value%0A%0ACells(z%20%2B%20y%2C%203).Value%20%3D%20Cells(i%2C%202).Value%0A%0ANext%20z%0A%0Ay%20%3D%20y%20%2B%20Cells(1%2C%202).Value%0A%0ANext%20i%0A%0AEnd%20With%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20code.%20You%20can%20click%20the%20button%20in%20cell%20E2%20in%20the%20attached%20file%20to%20start%20the%20macro.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3407915%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3407915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1394037%22%20target%3D%22_blank%22%3E%40ElRafaVaz33%3C%2FA%3E%26nbsp%3BThere%20are%20lots%20of%20possibilities.%26nbsp%3B%20Here%20is%20one%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(in%2CF1%3AF6%2Crep%2CG1%2Cs%2CSEQUENCE(ROWS(in)*rep%2C1%2C0)%2CINDEX(in%2CQUOTIENT(s%2Crep)%2B1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3407994%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3407994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1394037%22%20target%3D%22_blank%22%3E%40ElRafaVaz33%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20alternative%20could%20be%20Power%20Query%20like%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3408012%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3408012%22%20slang%3D%22en-US%22%3EPresuming%20names%20in%20A1%3AA3%3A%3CBR%20%2F%3E%3DTOCOL(CHOOSECOLS(A1%3AA3%2C1%2CSEQUENCE(3%2C1%2C1%2C0)))%3CBR%20%2F%3E%3CBR%20%2F%3E*TOCOL%20and%20CHOOSECOLS%20are%20available%20with%20Insider%20(beta%20channel).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3408608%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3408608%22%20slang%3D%22en-US%22%3EThis%20is%20perfect%2C%20thank%20you!%20I'd%20never%20heard%20of%20the%20LET%20function%2C%20but%20reading%20on%20it%2C%20it%20looks%20super%20powerful.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20anyone%20reading%20this%20after%20me%2C%20here%20the%20source%20list%20is%20given%20from%20F1%3AF6%20while%20G1%20holds%20the%20number%20of%20repetitions.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3408609%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3408609%22%20slang%3D%22en-US%22%3EThanks%20all%20for%20your%20answers!%20There%20definitely%20seems%20to%20be%20multiple%20ways%20to%20address%20this%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3408611%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20dynamic%20repeating%20sequence%20of%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3408611%22%20slang%3D%22en-US%22%3EYES%2C%20LET()%20is%20SUPER%20awesome%20addition.%20If%20you%20don't%20know%20about%20it%20definitely%20learn.%20Also%20look%20into%20FILTER%2C%20SORT%2C%20UNIQUE%2C%20and%20then%20LAMBDA%20(which%20gets%20a%20little%20more%20complicated%20but%20even%20more%20powerful).%20Some%20great%20things%20happening%20in%20Excelverse%3C%2FLINGO-BODY%3E
Occasional Contributor

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 string of the right number of elements, and then a Split function to separate them into rows.

 

However I haven't been able to find a way to do this in Excel; I get stumped on the split step. Would anyone have any suggestions on how to go about this? 

 

Here's a visual example of what I'm trying to achieve:
Screen Shot 2022-05-20 at 11.54.51 AM.png

For reference I'm using Office 365 on a mac. Perhaps I am thinking about this problem in the wrong way. Any direction I could investigate would be really helpful.

 

Thanks!

7 Replies

@ElRafaVaz33 

Sub names_array()

Dim i As Integer
Dim lngzeilemax As Integer
Dim z As Integer
Dim y As Integer

With Tabelle1

lngzeilemax = .Range("B" & .Rows.Count).End(xlUp).Row

For i = 3 To lngzeilemax * Cells(1, 2).Value

For z = 1 To Cells(1, 2).Value

Cells(z + y, 3).Value = Cells(i, 2).Value

Next z

y = y + Cells(1, 2).Value

Next i

End With

End Sub

Maybe with this code. You can click the button in cell E2 in the attached file to start the macro.

best response confirmed by ElRafaVaz33 (Occasional Contributor)
Solution

@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 

An alternative could be Power Query like in the attached file.

Presuming names in A1:A3:
=TOCOL(CHOOSECOLS(A1:A3,1,SEQUENCE(3,1,1,0)))

*TOCOL and CHOOSECOLS are available with Insider (beta channel).
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.
Thanks all for your answers! There definitely seems to be multiple ways to address this
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