Search code examples
excelexcel-formuladynamic-arrays

How to build a 2D dynamic array parameterised by a 1D dynamic array in Excel

Is there a way to create a dynamic array by concatenating multiple dynamic arrays in a for -like loop in Office 365 Excel?

Here is a MWE (my actual columns are more complex than just using randarray.

What I am trying to achieve is in cells B2:F4 in this figure:screenshot of desired outcome

The formulas as a screenshot... enter image description here

... and as text:

index: =SEQUENCE(,5)
desired: =RANDARRAY(3,5, B1#,B1#+1,TRUE)
workaround: =RANDARRAY(3,,B1,B1+1,TRUE)
attempt 1 (hstack): =HSTACK(RANDARRAY(3,,B1#,B1#+1,TRUE))
attempt 2 (bycol): =BYCOL(B$1#,LAMBDA(m,RANDARRAY(3,1,m,m+1,TRUE)))

I do notice (see screenshot) that there is a "Nested Array" error, That seems significant. I'd appreciate any feedback, examples, or clarifications, even if the answer is "not possible in 2023".

Solution

  • RANDARRAY Applied On Repeating Rows

    Repeat Rows

    • This 'repeats' the row 3 times resulting in d (imagine B1:F1 copied to B2:F4).
    • Then it uses d and d+1 with the RANDARRAY function.
    =LET(data,B1#,rows,3,increment,1,
    d,IF(SEQUENCE(rows),data),
RANDARRAY(COLUMNS(d),rows,d,d+increment,1))

    enter image description here

    MAKEARRAY (similar to DjC's suggestions in your comments)

    =LET(data,B1#,rows,3,increment,1,
MAKEARRAY(rows,COLUMNS(data),LAMBDA(r,c,
    LET(num,INDEX(data,c),
    RANDBETWEEN(num,num+increment)))))