Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
random permutation of an array
Can anyone help me with a script that would shuffle the elements of an array
of integers randomly. For example: MyArray before shuffling: 1, 2, 3, 4, 5 MyArray after shuffling: 3, 5, 4, 2, 1 I want the reordering to be really random. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
random permutation of an array
With the array in A1:A10;
Select B1:B10 (or any suitable vertical range) Use =RandomA(A1:A10); commit with CTRL+SHIFT+ENTER ----------------------------- Option Base 1 Function randomA(myrange) Dim myarray(100, 2) rcount = myrange.Count For j = 1 To rcount myarray(j, 1) = myrange(j) 'transfer data from cell to array myarray(j, 2) = Rnd 'give second vector a random value Next j For j = 1 To rcount - 1 'bubble sort by random number For k = j To rcount If myarray(k, 2) myarray(j, 2) Then tempa = myarray(j, 1) tempb = myarray(j, 2) myarray(j, 1) = myarray(k, 1) myarray(j, 2) = myarray(k, 2) myarray(k, 1) = tempa myarray(k, 2) = tempb End If Next k Next j randomA = myarray End Function ----------------------------------------- This uses a simple (inefficient but easy to code) sorting algorithm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Shatin" wrote in message ... Can anyone help me with a script that would shuffle the elements of an array of integers randomly. For example: MyArray before shuffling: 1, 2, 3, 4, 5 MyArray after shuffling: 3, 5, 4, 2, 1 I want the reordering to be really random. TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
random permutation of an array
You could try this
Sub Shuffle() Dim myarray(6) For x = 1 To 6 myarray(x) = x Debug.Print myarray(x) ActiveSheet.Cells(x, 1).Value = myarray(x) ActiveSheet.Cells(x, 1).Offset(, 1).Value = Rnd Next ActiveSheet.Range("A1:B6").Sort Key1:=Range("B1"), Order1:=xlAscending For x = 1 To 6 myarray(x) = ActiveSheet.Cells(x, 1).Value Next ActiveSheet.Range("A1:B6").ClearContents End Sub Mike "Shatin" wrote: Can anyone help me with a script that would shuffle the elements of an array of integers randomly. For example: MyArray before shuffling: 1, 2, 3, 4, 5 MyArray after shuffling: 3, 5, 4, 2, 1 I want the reordering to be really random. TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
random permutation of an array
My 2 cents:
'/===========================/ Sub Macro1() Dim aryData 'declare an array Dim i As Integer, iCount As Integer Dim strOldSheetName As String Dim strNewSheetName As String iCount = 5 '# of randoms ReDim aryData(iCount - 1) 're-dimension the array 'get name of current worksheet strOldSheetName = ActiveSheet.Name Sheets.Add 'create a 'working' worksheet 'get name of new worksheet strNewSheetName = ActiveSheet.Name 'fill data w/ values For i = 1 To iCount ActiveCell.Offset(i - 1, 0).Value = i Next i 'put random generator next to values For i = 1 To iCount ActiveCell.Offset(i - 1, 1).Value = "=RAND()" Next i 'sort randomly based on =RAND formula in Col B Range("A1:" & ActiveCell.Offset(iCount - 1, 1).Address).Sort _ Key1:=Range("B1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'put random #s into an array For i = 0 To iCount - 1 aryData(i) = ActiveCell.Offset(i).Value Next i 'delete the 'working' worksheet Sheets(strNewSheetName).Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True 'go to the original sheet Sheets(strOldSheetName).Select For i = 0 To iCount - 1 Debug.Print aryData(i) Next i Application.DisplayAlerts = True End Sub '/===========================/ -- Hope this helps. Thanks in advance for your feedback. Gary Brown "Shatin" wrote: Can anyone help me with a script that would shuffle the elements of an array of integers randomly. For example: MyArray before shuffling: 1, 2, 3, 4, 5 MyArray after shuffling: 3, 5, 4, 2, 1 I want the reordering to be really random. TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
random permutation of an array
The following is a generalized shuffling routine that I have posted in the
past over in the compiled VB newsgroups, but it works fine in the VBA world of Excel as well. Give it an array of elements and it will put them in random order and return the randomized elements back in the original array that was passed to it. It only visits *each* array element *once* so it is quick. The code takes care of running the Randomize statement one time only (which is all that is necessary). Sub RandomizeArray(ArrayIn As Variant) Dim X As Long Dim RandomIndex As Long Dim TempElement As Variant Static RanBefore As Boolean If Not RanBefore Then RanBefore = True Randomize End If If VarType(ArrayIn) = vbArray Then For X = UBound(ArrayIn) To LBound(ArrayIn) Step -1 RandomIndex = Int((X - LBound(ArrayIn) + 1) * _ Rnd + LBound(ArrayIn)) TempElement = ArrayIn(RandomIndex) ArrayIn(RandomIndex) = ArrayIn(X) ArrayIn(X) = TempElement Next Else 'The passed argument was not an array 'Put error handler here, such as . . . Beep End If End Sub After passing your array into the RandomizeArray subroutine, its elements will be randomly reordered. The passed array may be of any normal type -- integer, string, single, etc. The neat thing is, if you pass an already randomized array to this routine, those randomly ordered elements will be randomize -- sort of like shuffling an already shuffled deck of cards. Rick "Shatin" wrote in message ... Can anyone help me with a script that would shuffle the elements of an array of integers randomly. For example: MyArray before shuffling: 1, 2, 3, 4, 5 MyArray after shuffling: 3, 5, 4, 2, 1 I want the reordering to be really random. TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
random permutation of an array
Thanks to all for your help!
"Shatin" wrote in message ... Can anyone help me with a script that would shuffle the elements of an array of integers randomly. For example: MyArray before shuffling: 1, 2, 3, 4, 5 MyArray after shuffling: 3, 5, 4, 2, 1 I want the reordering to be really random. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Number Array | Excel Programming | |||
Discrete random numbers into an array? | Excel Programming | |||
permutation of values in 2 dimensional array | Excel Programming | |||
random mumber array | Excel Programming | |||
Creating a Combination or Permutation Array in Excel | Excel Programming |