How do I randomize a list

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.

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