Randomization

PROBLEM:
"I have a list of values I would like to mix up randomly.  Can this be done with formulas only?  With VBA?"

SOLUTION 1 - Macro      (sample file: RandomizeList.xls)
1. Place list in column A with no spaces in between.
2. Macro randomizes the list and shows new order in original cells

CODE

Option Explicit

Sub RandomizeList()
'Author:    Jerry Beaucaire
'Date:      7/22/2010
'Summary:   Randomize the order of a list in the original list range
Dim MyList  As Variant
Dim MyCol   As Long
Dim Itm     As Long
Dim Itm2    As Long
Dim buf     As String

'Set column where values exist: A=1, B=2, etc.
MyCol = 1

'Collect values into an array
MyList = Application.WorksheetFunction.Transpose(Range(Cells(1, MyCol), _
Cells(Rows.Count, MyCol)).SpecialCells(xlConstants).Value2)

'Mix up the values
Randomize
For Itm = LBound(MyList) To UBound(MyList) - 1
'select a random item from the array
Itm2 = Int((UBound(MyList) - Itm + 1) * Rnd + Itm)
'swap the list items
buf = MyList(Itm)
MyList(Itm) = MyList(Itm2)
MyList(Itm2) = buf
Next Itm

'randomized list back into original range
ActiveSheet.Cells(1, MyCol).Resize(UBound(MyList)).Value = _
Application.WorksheetFunction.Transpose(MyList)

End Sub

SOLUTION 2 - Formulas    (sample file:  RandomNumGenerator.xls)
1. Create a list of values to choose from, here we've done it in column J

J K L
3 OptionRange RandomKey
4 22 0.409801199335177 =RAND()
5 33 0.647588279625955
6 bird 0.131297132904366
7 44 0.814774015662451
8 hamster 0.572022890370215
9 66 0.514274728606499
10 fish 0.505200548521689
11 88 0.694315222740591
12 insect 0.942135512139144
13 10 0.631815126012199
14 cat 0.365998312356171
15 12 0.984478939846477
16 dog 0.693539486796346
17 13 0.445573610214189
18 lizard 0.075510389640549

3. Then in column A use this formula to grab values from column J using the changing values in column K:

=INDEX(\$J\$4:\$J\$18,MATCH(SMALL(\$K\$4:\$K\$18,ROW(A1)),\$K\$4:\$K\$18,0))
 A 1 Choices 2 dog 3 cat 4 12 5 22 6 fish 7 insect 8 13 9 10 10 bird 11 44 12 hamster 13 lizard 14 88 15 66 16 33

Nothing says "thanks" like a steak dinner!