Stable formula to randomly shuffle an Excel range. Does not change output on recalculation.
Syntax: =\SHUFFLE.STABLE(seed, range) => array
=LAMBDA(seed,range,
LET(
oc, COLUMNS(range),
r, TOCOL(range),
s, INT(IF(AND(seed>0,seed<1),seed*16777213,seed)),
randarr, SCAN(s,SEQUENCE(ROWS(r),1,0,0),
LAMBDA(acc,val,MOD(acc*6423135,16777213))
),
WRAPROWS(
CHOOSECOLS(
SORT(
HSTACK(
MAP(r,randarr,
LAMBDA(x,y,IF(x="",16777213,y))
),
r
),
1
),
2
),
oc
)
)
)
Documentation
Returns the source range or array in random order. Blank cells will always be pushed to the end (last row and column of the output).
This is a stable version of the \SHUFFLE function.
Seed is expected to be a large integer or a fraction.
Commentary
Please see other, related functions for explanation of the pseudorandom number algorithm used shuffle in this function.