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
  2. Add a =RAND() formula in the column adjacent

      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
    1Choices
    2dog
    3cat
    412
    522
    6fish
    7insect
    813
    910
    10bird
    1144
    12hamster
    13lizard
    1488
    1566
    1633


    Nothing says "thanks" like a steak dinner!
    PayPal - The safer, easier way to pay online!


    Comments