Results 1 to 13 of 13

20020725, 11:30 #1
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
random order (Excel 97 SR2 on Win 2000 SP2)
Given the ascending numerical sequence 1 to 20 inclusive, how do I turn it into a random sequence?
Each number must only be used once.
stuck

20020725, 11:43 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
Put the numbers 1...20 in cells A1:A20.
In cell B1, enter the formula =RAND()
With cell B1 selected, double click the fill grip (the small black square in the lower right corner).
This should fill the formula down to cell B20.
With cell B1 still selected, click one of the sort buttons. This sorts A1:B20 on the second (random) column.
Note: each time the spreadsheet is recalculated, the values in column B will change. So if you want a different order, you can sort on column B again.

20020725, 13:34 #3
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
Thanks, obvious when you know how.
(not) stuck

20020815, 10:15 #4
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
I've moved on a bit...
I now have a sheet that creates 10 random numbers from a given range, e.g. 1  80, via RAND(), pastes these numbers as values elsewhere (to 'fix' them) then goes on to do other things.
All is tickety boo except that sometimes RAND picks the same number twice from the given range. To correct this I can just run the macro again to get a new selection but I could build this into the code if I knew how to write the correct VBA code for the following loop:
For cell b6 to b15
If the cell above is the same as the current cell, start the again, otherwise proceed
end if
next cell
Any suggestions?
stuck

20020815, 11:24 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
Perhaps somebody else will come up with a solution that doesn't involve coding. In the meantime, here is a procedure to "draw without replacement".
The code is based on an example by Legare Coleman in <post#=54084>post 54084</post#>.
Sub GenerateRandom(aCell As Range, iCount As Integer, iMin As Integer, iMax As Integer)
Dim iUsed() As Integer
ReDim iUsed(1 To iCount)
Dim i As Integer, j As Integer, iRand As Integer
If iCount > (iMax  iMin + 1) Then
MsgBox "You can't generate more than " & (iMax  iMin + 1) & " different numbers."
Exit Sub
End If
Randomize
i = 1
Do While i <= iCount
iRand = Int((iMax  iMin + 1) * Rnd + iMin)
For j = 1 To i  1
If iRand = iUsed(j) Then
Exit For
End If
Next j
If j = i Then
iUsed(i) = iRand
i = i + 1
End If
Loop
For i = 1 To iCount
aCell.Offset(i  1, 0) = iUsed(i)
Next i
End Sub
aCell is the first cell you want to fill.
iCount is the number of number you want to generate.
iMin is the minimum number.
iMax is the maximum number.
To generate 10 numbers in the range of 1 to 80, starting in cell B9, use
GenerateRandom [B9], 10, 1, 80
You get an error message if you try to generate more numbers than possible.
HTH,
Hans

20020815, 13:56 #6
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
Hmm, this seems a bit more than I need. My random numbers get sorted which is why I asked for help with code to step down 10 cells asking:
for first cell to last cell
If current cell contents = contents of cell above then it's a duplicate so go back and run the code to create a new list.
Otherwise proceed.
Next cell
I'm sure it's trivial but my VBA is still in the feeble category
Thanks,
stuck

20020815, 14:22 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
What you propose is rather inefficient:
 create a random list
 sort it
 loop through the items to find a duplicate; if you find one start all over again.
If you're unlucky, you might have to create the complete random list several times before you have one without duplicates.
The code I proposed, builds a list without duplicates the first time (at the cost of some internal looping, of course). You can then sort this list and go on.

20020815, 14:42 #8
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
True but efficiency is not an issue, please humour me. I promise to implement your approach in version 2. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
stuck

20020815, 14:59 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,826
 Thanks
 136
 Thanked 482 Times in 459 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
How about instead of fixing 10 random numbers between 1 and 80 you 'make' say, 20. Then use Data Filter to extract unique values from this range, placing them somehwere else and then just taking the first 10.
zeddy

20020815, 14:59 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
Since I don't know what code you have now, the following is only meant as a basic idea. I haven't bothered to make it flexible.
Sub CreateList()
Dim i As Integer
' This is just an example of creating a random list
' Replace it by your own code
For i = 7 To 15
Cells(i, 2) = Int(Rnd * 80 + 1)
Next
' Sort the range
Range("B6:B15").Sort Range("B6")
' Check and recreate if necessary
For i = 7 To 15
If Cells(i, 2).Value = Cells(i  1, 2).Value Then
CreateList
Exit For
End If
Next
End Sub
Just for fun, try replacing 80 by a value between 10 and 15 and see how many times the list must be recreated before you get one without duplicates.

20020816, 12:44 #11
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
Thank you Hans,
The 'Check and recreate if necessary' bit was the hint I needed. My code is not pretty but it works well enough as demo.
Your help has been invaluable in getting me up another rung of the learning ladder.
(un)stuck(for a little while anyway) <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

20020816, 12:52 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
Hi (un)stuck,
Your user picture seems appropriate for being stuck. Maybe you should change it each time you get unstuck, and change it back when you get stuck again... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
Have a good weekend,
Hans

20020816, 13:52 #13
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: random order (Excel 97 SR2 on Win 2000 SP2)
You mean to something like this?
http://search.barnesandnoble.com/booksearc...isbn=0446390240