Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

how to randomize numbers in Excel?Helpful Member!(2) 

harrow (TechnicalUser) (OP)
21 Jan 02 2:21
I need to randomize the numbers 1-60 so they are completely shuffled up. I tried using RANDBETWEEN(1,60) to get a column full of the 60 numbers, but found that even though there were 60 random numbers, not all numbers between 1 and 60 were included, and there was some repitition.
Can anything in Excel help me? Or any of the other Office programs?
Helpful Member!(2)  JVFriederick (IS/IT--Management)
21 Jan 02 9:43
A simple way would be to construct 2 columns, the first with numbers 1-60, the second with the formula =RAND() copied for all 60 rows.

Sort the table based on the second column and you would have a randomized list.
SkipVought (Programmer)
21 Jan 02 11:02
Hi,
I don't think that JVF's solution works -- I don't think there's a way to do it in with a spreadsheet function,

cuz you hafta keep track o' wuts bin slekted  

Try this one.  Writes the list in col A...

Sub Randomize(nFrom As Integer, nThru As Integer)
    Dim iRand1() As Integer, nNbr As Integer
    Dim iRand2() As Integer
'load Rand1 with nbrs, clear Rand2
    ReDim iRand1(nThru - nFrom + 1)
    ReDim iRand2(nThru - nFrom + 1)

    For i = nFrom To nThru
        iRand1(i) = (i)
        iRand2(i) = 0
    Next
    
    nThru2 = nThru
    For i = nFrom To nThru
        nNbr = Rnd * (nThru2 - nFrom) + nFrom
        iRand2(i) = iRand1(nNbr)
    ' reorder Rand1
        k = 0
        For j = nFrom To nThru2
            If j <> nNbr Then
                iRand1(nFrom + k) = iRand1(j)
                k = k + 1
            End If
        Next j
        ReDim Preserve iRand1(k)
        nThru2 = nThru2 - 1
    Next
    
    For i = nFrom To nThru
        Cells(i, 1).Value = iRand2(i)
    Next
End Sub

Hope this works 4 U  

Skip,
metzgsk@voughtaircraft.com

SkipVought (Programmer)
21 Jan 02 11:17
There were some logic errors in Randomize above.

Oh, by the way, you must call Randomize with two arguments in another procedure...

Sub main()
    Randomize 1, 60
End Sub



Sub Randomize(nFrom As Integer, nThru As Integer)
    Dim iRand1() As Integer, nNbr As Integer
    Dim iRand2() As Integer
'load Rand1 with nbrs, clear Rand2
    ReDim iRand1(nFrom To nThru)
    ReDim iRand2(nFrom To nThru)

    For i = nFrom To nThru
        iRand1(i) = (i)
        iRand2(i) = 0
    Next
    
    nThru2 = nThru
    For i = nFrom To nThru
        nNbr = Rnd * (nThru2 - nFrom) + nFrom
        iRand2(i) = iRand1(nNbr)
    ' reorder Rand1
        k = 0
        For j = nFrom To nThru2
            If j <> nNbr Then
                iRand1(nFrom + k) = iRand1(j)
                k = k + 1
            End If
        Next j
        nThru2 = nThru2 - 1
        If nThru2 >= nFrom Then _
            ReDim Preserve iRand1(nFrom To nThru2)
    Next
    
    For i = nFrom To nThru
        Cells(i, 1).Value = iRand2(i)
    Next
End Sub

Sorry that I jumped the gun on the previous code.  

Skip,
metzgsk@voughtaircraft.com

jonsi (IS/IT--Management)
21 Jan 02 12:05
JVF's method is simple and effective. I used a similar method with the following (recorded) Macro. It uses Column A as the Control Column, B as the one that gets sorted and C as the Rand() between 0 and 1.

ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A1:A2").Select
    Selection.AutoFill Destination:=Range("A1:A60"), Type:=xlFillDefault
    Range("A1:A60").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=RAND()"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C60"), Type:=xlFillDefault
    Range("C1:C60").Select
    ActiveWindow.SmallScroll Down:=-66
    Range("B1:C60").Select
    Range("C1").Activate
    Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Columns("C:C").Select
    Selection.ClearContents
    Range("A1").Select
End Sub

I found it works ...great for Lottery Picks

Jonsi
SkipVought (Programmer)
21 Jan 02 14:04
JVF,
got EGG on my face --
I finally figured out what you and jonsi were doing  -- of course...

it works PERFECTLY!!

Hats off to a simple (except for this dummy) solution that is worthy of ANOTHER STAR.  

Skip,
metzgsk@voughtaircraft.com

JVFriederick (IS/IT--Management)
21 Jan 02 14:25
Happens to me all the time.

I looked at your answer briefly and thought I must have missed something in the question.

Thanks for the star.  It's been hard to slip in a reply lately before someone else.

Hat's off to you !!
harrow (TechnicalUser) (OP)
23 Jan 02 4:09
thanks everyone!

i didn't quite get JVF's method at first either, but then i figured it out and got my column of random numbers!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close