Back to Forms in Excel VBA archive index

Back to archive home

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 | ||||

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.

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

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.

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.