Reordering a List

Posted by Steve Mathews on January 29, 2002 2:15 PM

What would be an efficient method of reordering a list in a random fashion? For example if I have 5 items, 1,2,3,4 and 5, I want them to be randomly reordered to something like 2,5,1,4 and 3. I can't think of how to do this in code.
Thanks for the help.

Posted by Igor on January 29, 2002 9:42 PM

Don't know if this will really help, but you can give a standard value, say 1, to all your items, and multiply them by a random number between 0 and 1 using the rand() function, then sort on the results. The list should change every time you do a sort.

Hope it helps,

Igor

Posted by Bariloche on January 29, 2002 10:04 PM

Steve,

Put your list of values in cells A1 thru Awhatever and then run this code.

Option Base 1Sub ReOrder()
    Dim aryList() As Variant
    Dim i As Long
    Dim j As Long
    Dim Temp As Variant
    Dim LastRow As Long

    LastRow = Cells(65536, 1).End(xlUp).Row
    ReDim Preserve aryList(LastRow)
' Read in values in column A starting at A1
    For i = 1 To LastRow
        aryList(i) = Cells(i, 1).Value
    Next i
'  Swap random elements
    For i = 1 To LastRow
        Randomize
        j = Int((LastRow - 1 + 1) * Rnd + 1)
        Temp = aryList(i)
        aryList(i) = aryList(j)
        aryList(j) = Temp
    Next i
'  Print out the randomized list
    For i = 1 To LastRow
        Cells(i, 2).Value = aryList(i)
    Next i
End Sub

Note that in this routine a value could get "swapped" with itself, but that is part of the "randomness." To not permit that would require putting in some additional checking.

enjoy