Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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.

Check out our Excel Resources

Re: How do I randomize a 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.


Re: How do I randomize a list

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
=IF(A1="","",RAND())
and the formula in C1 to
=IF(ISERROR(INDEX(A:A,COUNTA(A:A)-RANK(B1,B:B))),"",INDEX(A:A,COUNTA(A:A)-RANK(B1,B:B)))
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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.