Let’s see how to shuffle the records of a dataset using the RAND, LARGE, ROWS and VLOOKUP functions in Excel.
Step 1:
Insert a column on the left side of the dataset and generate an array of random numbers in it. The RAND Function in Excel can be used for this purpose.
=RAND()
Step 2:
These random numbers are then sorted in their descending order using the LARGE and ROWS function.
=LARGE($B$3:$B$17,ROWS($B$3:B3))
Step 3:
The sorted values are then used inside the VLOOKUP function to extract the records against them.
=VLOOKUP(F3,$B$3:$D$17,2,FALSE)
And every time when we recalculate the sheet (Press F9) a new set of random numbers are generated and the records are rearranged.
