This can be done with theSORTBY functionand theRANDARRAY function.
SORTBY function
The SORTBY function sorts provided values by one or more “sort by” arrays.
The sort by arrays make it possible to sort data in a completely custom manner.
That means we need a sort by array that contains 12 values.
To generate a random array of numbers to sort with, we need another function.
RANDARRAY function
The RANDARRAY function generates an array of random numbers between two values.
The size or the array is determined byrowsandcolumnsarguments.
This array is returned directly to the SORTBY function as theby_array1argument.
The range or array used to sort does not need to appear in results.
RANDARRAY Function
The Excel RANDARRAY function generates an array of random numbers between two values.
The size or the array is specified byrowsandcolumnsarguments.
The generated values can be either decimals or whole numbers.
ROWS Function
The Excel ROWS function returns the count of rows in a given reference.
Related videos
New dynamic array functions in Excel
Sort by custom list with SORTBY
