INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a Computer / IT professional? Join TekTips now!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Donate Today!
Do you enjoy these technical forums?
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.

how to randomize numbers in Excel?(2)

harrow (TechnicalUser) (OP) 
21 Jan 02 2:21 
I need to randomize the numbers 160 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? 

A simple way would be to construct 2 columns, the first with numbers 160, 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. 

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 

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/ITManagement) 
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 

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 

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! 



