random number list

  • Thread starter Thread starter momiage
  • Start date Start date
M

momiage

This is what I am trying to do...

Generate a random number 1 to 10
store the number, but make sure that it only appears once

I can generate the random number, and put it in an array, but I can'
get the array to be the ten numbers with no repeats. Any help would b
appriciated
 
How about putting 1 to 10 in A1:A10 and then put =rand() in B1:B10.

then sort A1:B10 by column B.
 
How can it be random and not be allowed to repeat?
The reason you are getting repeats is probably that you round
the values to an integer. However one way you could do it
do tools>options>calculations and turn on iteration

Leave A1:A10 blank, in B1 put

=IF(A1="","",IF(B1="",RAND(),B1))

copy down to B10

in A1 put 1, copy down to a10 this part is
just to trigger the calculation in B1:B10 so you can use
anything in A1:A10 just fill it with something.
Now B1:B10 should have 10 decimal values,
In C1 put

=RANK(B1,$B$1:$B$10)

copy down to C10

you can also skip the iteration and A1:A10 and just copy down the rand
formula

=RAND()

then use the rank to get the 1-10 numbers, then copy and paste special as
values
in place to make it independent of B1:B10
The A and the IF formula is only there to lock the random numbers
 
Try this macro: I do not remember whom the author is.

'This UDF will generate x unique random numbers between any 2 numbers you
specify. Many thanks to J.E. McGimpsey for modifying this to work on more
than 10 numbers.

'The Code

'To use this UDF push Alt+F11 and go Insert>Module and paste in the code.
Push Alt+Q and save. The Function will appear under "User Defined" in the
Paste Function dialog box (Shift+F3). Use the Function in any cell as shown
below.

'=RandLotto(1,20,8)
'This would produce 8 unique random numbers between 1 and 20

Function RandLotto(Bottom As Integer, Top As Integer, _
Amount As Integer) As String
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i
For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i - Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
For i = Bottom To Bottom + Amount - 1
RandLotto = RandLotto & " " & iArr(i)
Next i
RandLotto = Trim(RandLotto)
End Function


Hope this helps. Once the above is done you must copy it then paste the
"value" then seperate using Data, Text to column.

Gilles Desjardins
 
momiage,

Use the following code (author unknown to me), is very simple and elegant.

Jack Sons
The Netherlands

Sub RandNum1000()
Dim i As Integer, Pick As Integer, End As Integer
End = 10 'for random numbers from 1 up to 10
Dim Num(1000) As Integer '1000 or any other value larger than End

For i = 1 To End Num(i) = i
Next
For i = 1 To End
Pick = 1 + Int(Rnd * (End+ 1 - i))
ActiveCell.Offset(i - 1, 0).Value = Num(Pick)
Num(Pick) = Num(End+ 1 - i)
Next
End Sub
 
Back
Top