where "data" is the named range B5:B14. The result is a new random sort order whenever the worksheet is recalculated.
How this formula works
The SORTBY function allows sorting based on one or more "sort by" arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the first 10 letters of the alphabet. To generate 10 random numbers to sort with, we are using the RANDARRAY function together with the COUNTA function like this:
This array is provided to the SORTBY function as the by_array1 argument. SORTBY uses the random values to sort the data, and returns the 10 letters into a spill range starting in D5.
Note: RANDARRAY is a volatile function and will recalculate every time the worksheet is changed, causing values to be resorted. To stop values from sorting automatically, you can copy the formulas, then use Paste Special > Values to convert formulas to static values.
To sort by one column, you can use the SORT function or SORTBY function . In the example shown, data is sorted by the Group column. The formula in F5 is: = SORT ( B5:D14 , 3 ) Notice data is sorted in ascending order (A-Z) by default. How this...
To sort by two columns, you can use the SORTBY function . In the example shown, data is sorted first by the Group column in ascending order, then by the Score column in descending order. The formula in F5 is: = SORTBY ( B5:D14 , D5:D14 , 1 , C5:C14...
To sort a list in a custom order, you can combine the SORTBY function with the MATCH function . In the example shown, The table is being sorted by the "group" column using in the order shown in cells J5:J7. The formula in D5 is: = SORTBY ( B5:D14 ,...
The Excel RANDARRAY function generates an array of random numbers between. The size or the array is is specified by rows and columns arguments. Generated values can be either decimals or whole numbers.
The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.