Thread: random order (Excel 97 SR2 on Win 2000 SP2)

1. 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

2. 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.

3. Re: random order (Excel 97 SR2 on Win 2000 SP2)

Thanks, obvious when you know how.

(not) stuck

4. 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

5. 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

6. 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

7. 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.

8. 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

9. 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

10. 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 re-created before you get one without duplicates.

11. 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>

12. 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

13. Re: random order (Excel 97 SR2 on Win 2000 SP2)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•