MrExcel.com - Your One Stop for Excel Tips & Solutions

A Random Sort Using SORT and RANDARRAY

August 15, 2022 - by

A Random Sort Using SORT and RANDARRAY

Difficult scenarios like random drug testing and random with no repeats become mind-numbingly simple when you combine SORT with RANDARRAY.

Say that you want to sort 13 names randomly and without repeats. To do this, you use =SORTBY(A4:A16,RANDARRAY(13)) , as shown below.

To randomly sort for a drug test, use =SORTBY(A4:A16, but then specify RANDARRAY(13) as the by_array argument.
Figure 614. Sorting randomly without repeats.

The SORTBY function's syntax is =SORTBY(array, by_array1, sort_order1,)...

The syntax of SORTBY is array, by array, sort order, .... The first two arguments are required. You can repeat by_array1 and sort_order1 up to 126 times.
Figure 615. It is possible to sort by something else.

Say that you want to sort by team and then score, and you want to show only the names. In this case, you can use SORTBY as shown here.

The original data has name, team, score. This formula sorts by team and score, but only gives you the names. =SORTBY(A4:A16,B4:16,1,C4:C16,-1).
Figure 616. Sorting column

This article is an excerpt from Power Excel With MrExcel

Title photo by Alex Block on Unsplash