May 20 2022 11:57 AM - last edited on Nov 09 2023 11:09 AM by
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:
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!
May 20 2022 01:14 PM
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.
May 20 2022 02:43 PM
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))
May 20 2022 02:54 PM
An alternative could be Power Query like in the attached file.
May 20 2022 02:57 PM
May 20 2022 07:40 PM
May 20 2022 07:43 PM
May 20 2022 07:49 PM
Mar 22 2023 06:29 PM
@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?
Mar 23 2023 10:41 AM
With REDUCE
list - list of names to repeat
rep - number of times to repeat each
=REDUCE("Result",list,LAMBDA(a,v,VSTACK(a,EXPAND(v,rep,,v))))
Mar 23 2023 11:52 AM
@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 :)
Apr 29 2023 04:09 AM - edited Apr 29 2023 04:13 AM
D2#: =LET(a;A3:A5;b;B1;INDEX(a;SEQUENZ(ZEILEN(a)*b;;;1/b))) deutsch
D2#: =LET(a,A3:A5,b,B1,INDEX(a,SEQUENCE(ROWS(a)*b,,,1/b))) english (US)
makes it variable only on A3:A5 (and implicitly its count) and B1
Apr 29 2023 07:28 AM
One more for the collection
=REDUCE(
"Result",
MAP(list, LAMBDA(v, LAMBDA(IF(SEQUENCE(n), v)))),
LAMBDA(a,v, VSTACK(a, v()))
)
Close to what @Patrick2788 suggested.
Apr 30 2023 08:29 AM
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'!
May 01 2023 11:43 AM
=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.
Feb 08 2024 06:13 PM
Feb 09 2024 08:32 AM
@fsdesloge and @ElRafaVaz33 With the functions now available in Excel for MS365, this can simply be achieved with TOCOL, IF and SEQUENCE:
=TOCOL(IF(SEQUENCE(,B1), A4:A6))
For a full explanation of this method, please see: https://techcommunity.microsoft.com/t5/excel/copy-array-n-times-with-excel-formula/m-p/4033181#M2178...
Feb 09 2024 09:04 AM
@djclements - great - thanks!
May 20 2022 02:43 PM
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))