Randomized Functions
 

how do i do to make Excel to generate random numbers to 30 persons?, but one
detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i couldn't
avoid repeated numbers...

Randomized Functions
 

One play to try ..

List the numbers: 1 - 30 into A1:A30
Put in B1: =RAND()
Copy B1 down to B30

Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$30))
Copy C1 down to C30

C1:C30 will return a random shuffle
of the numbers: 1-30 (non-repeating)

Press F9 to regenerate

We could simply replace A1:A30
with whatever 30 unique numbers (other than 1-30)
or items that is to randomly shuffled
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kleverton Silva" <Kleverton wrote in
message ...
how do i do to make Excel to generate random numbers to 30 persons?, but
one
detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i
couldn't
avoid repeated numbers...

Randomized Functions
 

'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 InsertModule 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

Works great for me

Gilles
"Kleverton Silva" <Kleverton wrote in
message ...
how do i do to make Excel to generate random numbers to 30 persons?, but
one
detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i
couldn't
avoid repeated numbers...

Randomized Functions
 

both helps, but, on the Max's Advice, the numbers appeared in the same order
they were on Colum A:A... not randomized or shuffled... and on Gilles
Desjardins's Advice all the number appeared concatenated in the same cell...
but thanks for the help... i'll study both advices to see if i can solve from
here... but if you two know how to proceed from here... i'll be pleasure...
thanks!

"Gilles Desjardins" escreveu:

'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 InsertModule 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

Works great for me

Gilles
"Kleverton Silva" <Kleverton wrote in
message ...
how do i do to make Excel to generate random numbers to 30 persons?, but
one
detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i
couldn't
avoid repeated numbers...

Randomized Functions
 

"Kleverton Silva" wrote
.. on Max's .. the numbers appeared
in the same order they were on Colum A:A
... not randomized or shuffled

Don't know what happened over there,
but here's a sample working construct to play with:
http://cjoint.com/?lqhmUmbaFV
KlevertonSilva_misc.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Randomized Functions
 

ok... problem solved.. thanks Max!!!

"Max" escreveu:

"Kleverton Silva" wrote
.. on Max's .. the numbers appeared
in the same order they were on Colum A:A
... not randomized or shuffled

Don't know what happened over there,
but here's a sample working construct to play with:
http://cjoint.com/?lqhmUmbaFV
KlevertonSilva_misc.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Randomized Functions
 

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Kleverton Silva" wrote in
message ...
ok... problem solved.. thanks Max!!!

Randomized Functions
 

Once the numbers are created you just convert: Data, text to columns

Gilles
"Kleverton Silva" <Kleverton wrote in
message ...
how do i do to make Excel to generate random numbers to 30 persons?, but
one
detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i
couldn't
avoid repeated numbers...