Creating a dynamic repeating sequence of arrays

ElRafaVaz33 

A recursive solution with an option for directional output vertical or horizontal.

RepeatElements = LAMBDA(arr, reps, [direction],
        LET(
            vector, IF(ISOMITTED(direction), TOCOL(arr), TOROW(arr)),
            acc, TAKE(arr, , 1),
            stack, HSTACK(acc, arr),
            IF(reps = 1, vector, RepeatElements(stack, reps - 1, direction))
        )
    )

ElRafaVaz33 

The attached workbook contains the basic CHOOSECOLS/TOCOL solution as well as the REDUCE/VSTACK approach.  The main intention, however, was to test a function BYROWλ against the different use cases.  The function description is

/* DESCRIPTION:    Implements a version of BYROW that will return an array of arrays */

It is used first simply to create the 4 repetition problem and then the variable repetition version.  In each case the array of array problem of Excel is solved out of sight of the user.

 

Note: The variable repetition version would appear neater were the MAP helper function generalised but I have yet to tackle that 

ElRafaVaz33 

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'!

ElRafaVaz33 

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.

ElRafaVaz33 

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))))

ElRafaVaz33 

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

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.