Search code examples
excelexcel-formuladynamic-arrays

How to create one dynamic array in Excel that is a concatenation of several dynamic arrays?


I have a function that returns a column. For a Minimal Working Example, please consider a name randperm as:

=LAMBDA(n,
    LET(ra, RANDARRAY(n),
        sorted_ra, SORT(ra), 
        XMATCH(ra, sorted_ra)
    )
)

Now what I'd like to get is (say) 5 different random permutations of (say) 4 numbers in a 4 x 5 array.

The most promising try has been:

=IF(sequence(,5),randperm(4))

which correctly produces a 4 x 5 array. But all the columns are the same.


Solution

  • Horizontally Stack Random Columns

    • Can't see anything beyond REDUCE combined with HSTACK:
    =LET(n,4,cols,5,
    REDUCE(RandPerm(n),SEQUENCE(cols-1),
        LAMBDA(rr,r,HSTACK(rr,RandPerm(n)))))
    

    enter image description here