Formula Recipe:Random order list

To transform a given list of item in random order, the following formula could be used:
RAND - for creating random number
ROW - used with RAND to generate random number
LARGE - To match out the list from ranking.
VLOOKUP - used with LARGE for locate exact position of item

Assume the list of Target is located in column B


Step1: add formula in A2:
=INT(RAND()*1000)+ROW()/10000
the "ROW()/10000" is used to prevent duplicated random number.Even the rand() result is 0, the default ranking would be row number. The 10000 is the possible number of items in the list.

Step 2: add formula in D2:
=LARGE(A$2:A$21,ROW()-1)

To generate sorted list of rank
Step 3: add formula in E2
=VLOOKUP(D2,A$1:B$21,2,FALSE)
Based on the sorted list of rank, look for the sorted list of targets.

Step 4: extend the formula in A2,D2 and E2 to number items by copy and paste

Finish!