Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    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

  2. #2
    Plutonium Lounger
    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.

  3. #3
    5 Star Lounger
    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

  4. #4
    5 Star Lounger
    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

  5. #5
    Plutonium Lounger
    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

  6. #6
    5 Star Lounger
    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

  7. #7
    Plutonium Lounger
    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.

  8. #8
    5 Star Lounger
    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

  9. #9
    WS Lounge VIP
    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

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

  11. #11
    5 Star Lounger
    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>

  12. #12
    Plutonium Lounger
    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

  13. #13
    5 Star Lounger
    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)


Posting Permissions

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