How do I get random numbers from a certain range?

[Random numbers]

I have 468 unique numbers and need to select, at random, 64 numbers that do
not repeat. How do I get Excel to do this?

 

[Bernie Deitrick]

Assuming your numbers are in A2:A469...

In B2:B469, enter =RAND() and then sort A and B based on B, and take the top
64 (A2:A65)

OR: you can use formulas to pull a different set of 64 each time you
re-calc, which may not be what you want.

HTH,
Bernie
MS Excel MVP

 

[Gary''s Student]

Put your values in B1 thru B468.

In A1 enter:
=RAND() and copy down

In C1 thru C64 enter:

=VLOOKUP(LARGE(A$1:A$468,ROW()),$A$1:$B$468,2,FALSE)

 

[Bernd P]

Hello,

If your numbers are in A1:A468, select 64 cells and array-enter
=Random_Pick(A1:A468)

My UDF Random_Pick you can get here:
http://sulprobil.com/html/random_pick.html

Regards,
Bernd

 

[Bill Kuunders]

Set up a table in Dand E columns with the D column from 1 to 468
and E column is your list of unique numbers
in B column you enter a vlookup formula and extend it to 64 rows
=VLOOKUP(A1,$D$1:$E$468,2,FALSE)

Assign the following macro to a button

Sub TheBestLuckyLottoPicker()
Dim t As Integer, m As Integer
k = 64: n = 468
Do While m < k
Randomize
If (n - t) * Rnd() < k - m Then
m = m + 1
Cells(m, 1) = t + 1
End If
t = t + 1
Loop
End Sub

This macro will produce 64 random numbers from 1 to 468
they will be sorted in the A column
the lookup formula will give you your unique numbers.

Greetings from New Zealand

 

[Joe User]

I have 468 unique numbers and need to select, at random, 64 numbers that
do not repeat. How do I get Excel to do this?

Suppose your numbers are in X1:X468. In some other column, e.g. Y1:Y468
(although it does not need to be parallel), enter =RAND(), starting with Y1
and copying down. Then in A1:A64, put the following formula, starting with
A1 and copying down:

=INDEX($X$1:$X$468, RANK(Y1,$Y$1:$Y$468))

Pay close attention to what's a relative or absolute reference.

Note: Since RAND is a volatile function, you will find that A1:A64 changes
every time any cell is modified manually on any worksheet in the workbook
:-(. Probably not what you want. To avoid that,
copy-and-paste-special-values Y1:Y468 back onto itself. If/when you want to
generate another set of numbers, you would have to fill Y1:Y468 with =RAND()
again. Alternatively, use myRand below instead RAND:

Function myRand(Optional arg) As Double
Static first As Integer
If first = 0 Then Randomize: first = 1
myRand = Rnd()
End Function

If you use myRand exactly as you use RAND -- i.e. no argument -- you can
generate another set of numbers by pressing ctrl+alt+F9.

Alternatively, if you use myRand($B$1), for example, you can generate
another set of numbers simply by editing B1, for example by pressing F2,
then Enter.