Results 1 to 6 of 6

20030207, 21:14 #1
 Join Date
 Mar 2001
 Posts
 309
 Thanks
 0
 Thanked 0 Times in 0 Posts
Random list of numbers (Excel 2002)
I have been asked to develop a random list of numbers that will be used by all students during the housing selection at our school. However, I do not know how to do that in Excel. Help?

20030207, 22:29 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Random list of numbers (Excel 2002)
You can use the RANDBETWEEN function to generate random numbers. For example, if you place the following function:
<pre>=RANDBETWEEN(1,100)
</pre>
it will generate a random number between 1 and 100. If you then fill it down for as many cells as you need random numbers, you will have a list. However, there may be duplicates in the list. If this is a problem, then you will need a VBA macro to generate the list.Legare Coleman

20030207, 22:48 #3
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Random list of numbers (Excel 2002)
Legare's reply is what I would use. I wasn't sure when the RANDBETWEEN function became available (Which version of Excel). So here's one other way...
1. In a cell, you simply type: <font color=blue>=RAND()</font color=blue>. And tap the [ENTER} key.
2. This will generate a random number in the selected cell. The number will be a decimal number between 0 and 1.
3. You can see in the example that I've used: <font color=blue>=RAND()*100</font color=blue> to get a random number between 0 and 100.
4. You will have to rightclick on the cell, choose format cells, and put a zero in the decimal places to guarantee a clean, whole number.
5. Then, you can just copy the cell to as many other cells as you like to get your list.
Also, you should know that the list will change everytime the sheet recalculates. You'll need to let us know if you want the random list to remain constant.
If you want the possibility of larger numbers, then change the number <font color=blue>100</font color=blue> in the formula to 1000 or 10000, etc. Ricky

20030208, 15:39 #4
 Join Date
 Mar 2001
 Posts
 309
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Random list of numbers (Excel 2002)
I guess I did not ask the question well. I do not want to reuse any number. So if we have a range of 1  200, I would like all the numbers scrambled so I will have the same 1  200 but in random order. I got duplicates this way.
Thanks.

20030208, 16:23 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Random list of numbers (Excel 2002)
Here is a VBA macro that will generate a list of the integers between 1 and 200 in random order in Sheet1!A1:A200.
<pre>Public Sub GenerateList()
Dim iNum(1 To 200) As Integer, iMax As Integer
Dim I As Integer, J As Integer, iRand As Integer
For I = 1 To 200
iNum(I) = I
Next I
Randomize
iMax = 200
For I = 1 To 200
iRand = Int(iMax * Rnd + 1)
Worksheets("Sheet1").Range("A1").Offset(I  1, 0).Value = iNum(iRand)
For J = iRand To iMax  1
iNum(J) = iNum(J + 1)
Next J
iMax = iMax  1
Next I
End Sub
</pre>
Legare Coleman

20030208, 20:16 #6
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Random list of numbers (Excel 2002)
No Need for VBA:
1) In column A, create the numbers 1 to 200 in order. Enter 1 & 2, then auto fill to 200.
2) in cell B1, enter the formula =RAND()
3) doubleclick the square in the lowerright corner of B1. This autofills B1 thru B200.
4) press F9 to recalculate
5) Select columns A & B and use the Data  Sort menu to sort by column B
6) Select & delete column B
HTH Sam<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>