MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I randomize a list

Posted by Chuck Reul on January 08, 2001 2:36 PM

I have made a vocabulary list for a course I teach in one column in Excel 97. I would like a macro or method to repeatedly randomize the list.

Posted by Mark W. on January 08, 2001 3:24 PM

Suppose cells A1:A4 contain {"cat";"dog";"mouse";"bird"}.
Enter the formula, =RAND(), in cell B1 and copy down.
Next, enter the array formula, {=INDEX($A$1:$A$4,COUNTA(A:A)-SUM((B1>$B$1:$B$4)+0))},
into cell C1 and copy down. If you're not familiar with array
formula, you don't enter the enclosing braces, {}. Excel adds
them when the formula is entered when you use the
Shift+Ctrl+Enter key combination.

Once you worksheet is setup as described above you
can press Ctrl+= (control equal sign) to reshuffle
the list in column C.

Posted by Chuck R on January 09, 2001 4:16 PM

Mark W

I tried your suggestion and it sort of half worked so I played around with it and used a different formula in cell C1 and then copied it down. The formula I used is =INDEX(A:A,COUNTA(A:A)-RANK(B1,B:B)). Since I am using the INDEX function in its Reference form, I did not need the array formula. This works perfectly.

The second modification I made was to make it so the list could be added to indefinitely and not produce error signals in columns B and C. I did this by changing the formula in B1 to
and the formula in C1 to
Then copied both of these several hundred rows down.

Thanks for the help. I would not have found the solution without your help.

Chuck R