Random number generation from a set of non-sequencial values
 

I'm not sure that Excel can do this, and I've never needed to try anything
like it in the past, so I'm not even sure how to attempt it. Basically, I
have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16, 17, 19,
20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I want
to generate sets of 5 or 6 random numbers from among them, and ONLY from
among those number.

The random number generation function native to Excel ( RAND()*(b-a)+a or
RAND()*100 ) would give me a true random number, or random number from
0-100 for example, but is there any way I could exclude all but specific
numbers from the value range and have it pick 5 values from my set as I
described?

Hopefully I've described what I'm after well enough that it makes sense.
Thank you in advance.

KM

Random number generation from a set of non-sequencial values
 

Hi KM

The most common way to make this "excel lottery" is to have the contestants
(here your numbers) downwards in A column, and formula =RAND() in B column.
Sort by B, top n in A are winners.

There are also code solutions. Here is a simple one, replace "sName"
assignments and number of contestants with something useful:

Type Man
sName As String
LNumber As Double
End Type

Sub Draw()
Dim ThisMan As Man
Dim Men() As Man
Dim L As Long, M As Long

Randomize
'read:
ReDim Men(1 To 6)
Men(1).sName = "A"
Men(1).LNumber = Rnd()
Men(2).sName = "B"
Men(2).LNumber = Rnd()
Men(3).sName = "C"
Men(3).LNumber = Rnd()
Men(4).sName = "D"
Men(4).LNumber = Rnd()
Men(5).sName = "E"
Men(5).LNumber = Rnd()
Men(6).sName = "F"
Men(6).LNumber = Rnd()
'sort:
For L = 1 To 5
For M = 1 To 5
If Men(M).LNumber Men(M + 1).LNumber Then
ThisMan.LNumber = Men(M + 1).LNumber
ThisMan.sName = Men(M + 1).sName
Men(M + 1).LNumber = Men(M).LNumber
Men(M + 1).sName = Men(M).sName
Men(M).LNumber = ThisMan.LNumber
Men(M).sName = ThisMan.sName
End If
Next M
Next L
'prompt:
MsgBox "Winner is " & Men(6).sName
MsgBox "#2 is " & Men(5).sName
End Sub

HTH. Best wishes Harald

"KevinMyers" skrev i melding
news:Mnxvd.5075$2r.2285@fed1read02...
I'm not sure that Excel can do this, and I've never needed to try anything
like it in the past, so I'm not even sure how to attempt it. Basically, I
have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16, 17,
19,
20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I
want
to generate sets of 5 or 6 random numbers from among them, and ONLY from
among those number.

The random number generation function native to Excel ( RAND()*(b-a)+a or
RAND()*100 ) would give me a true random number, or random number from
0-100 for example, but is there any way I could exclude all but specific
numbers from the value range and have it pick 5 values from my set as I
described?

Hopefully I've described what I'm after well enough that it makes sense.
Thank you in advance.

KM

Random number generation from a set of non-sequencial values
 

Excellent! Thanks so much, Harald. The first option is so simple and it's
perfect for the quicky job I'm doing. Thanks again.

KM


"Harald Staff" wrote in message
...
Hi KM

The most common way to make this "excel lottery" is to have the
contestants
(here your numbers) downwards in A column, and formula =RAND() in B
column.
Sort by B, top n in A are winners.

There are also code solutions. Here is a simple one, replace "sName"
assignments and number of contestants with something useful:

Type Man
sName As String
LNumber As Double
End Type

Sub Draw()
Dim ThisMan As Man
Dim Men() As Man
Dim L As Long, M As Long

Randomize
'read:
ReDim Men(1 To 6)
Men(1).sName = "A"
Men(1).LNumber = Rnd()
Men(2).sName = "B"
Men(2).LNumber = Rnd()
Men(3).sName = "C"
Men(3).LNumber = Rnd()
Men(4).sName = "D"
Men(4).LNumber = Rnd()
Men(5).sName = "E"
Men(5).LNumber = Rnd()
Men(6).sName = "F"
Men(6).LNumber = Rnd()
'sort:
For L = 1 To 5
For M = 1 To 5
If Men(M).LNumber Men(M + 1).LNumber Then
ThisMan.LNumber = Men(M + 1).LNumber
ThisMan.sName = Men(M + 1).sName
Men(M + 1).LNumber = Men(M).LNumber
Men(M + 1).sName = Men(M).sName
Men(M).LNumber = ThisMan.LNumber
Men(M).sName = ThisMan.sName
End If
Next M
Next L
'prompt:
MsgBox "Winner is " & Men(6).sName
MsgBox "#2 is " & Men(5).sName
End Sub

HTH. Best wishes Harald

"KevinMyers" skrev i melding
news:Mnxvd.5075$2r.2285@fed1read02...
I'm not sure that Excel can do this, and I've never needed to try
anything
like it in the past, so I'm not even sure how to attempt it. Basically,
I
have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16, 17,
19,
20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I
want
to generate sets of 5 or 6 random numbers from among them, and ONLY from
among those number.

The random number generation function native to Excel ( RAND()*(b-a)+a
or
RAND()*100 ) would give me a true random number, or random number from
0-100 for example, but is there any way I could exclude all but specific
numbers from the value range and have it pick 5 values from my set as I
described?

Hopefully I've described what I'm after well enough that it makes sense.
Thank you in advance.

KM

Random number generation from a set of non-sequencial values
 

Great. Thanks for the feedback.
Best wishes Harald

"KevinMyers" skrev i melding
news:XHxvd.5077$2r.2359@fed1read02...
Excellent! Thanks so much, Harald. The first option is so simple and it's
perfect for the quicky job I'm doing. Thanks again.

KM


"Harald Staff" wrote in message
...
Hi KM

The most common way to make this "excel lottery" is to have the
contestants
(here your numbers) downwards in A column, and formula =RAND() in B
column.
Sort by B, top n in A are winners.

There are also code solutions. Here is a simple one, replace "sName"
assignments and number of contestants with something useful:

Type Man
sName As String
LNumber As Double
End Type

Sub Draw()
Dim ThisMan As Man
Dim Men() As Man
Dim L As Long, M As Long

Randomize
'read:
ReDim Men(1 To 6)
Men(1).sName = "A"
Men(1).LNumber = Rnd()
Men(2).sName = "B"
Men(2).LNumber = Rnd()
Men(3).sName = "C"
Men(3).LNumber = Rnd()
Men(4).sName = "D"
Men(4).LNumber = Rnd()
Men(5).sName = "E"
Men(5).LNumber = Rnd()
Men(6).sName = "F"
Men(6).LNumber = Rnd()
'sort:
For L = 1 To 5
For M = 1 To 5
If Men(M).LNumber Men(M + 1).LNumber Then
ThisMan.LNumber = Men(M + 1).LNumber
ThisMan.sName = Men(M + 1).sName
Men(M + 1).LNumber = Men(M).LNumber
Men(M + 1).sName = Men(M).sName
Men(M).LNumber = ThisMan.LNumber
Men(M).sName = ThisMan.sName
End If
Next M
Next L
'prompt:
MsgBox "Winner is " & Men(6).sName
MsgBox "#2 is " & Men(5).sName
End Sub

HTH. Best wishes Harald

"KevinMyers" skrev i melding
news:Mnxvd.5075$2r.2285@fed1read02...
I'm not sure that Excel can do this, and I've never needed to try
anything
like it in the past, so I'm not even sure how to attempt it.
Basically,
I
have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16,
17,
19,
20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I
want
to generate sets of 5 or 6 random numbers from among them, and ONLY
from
among those number.

The random number generation function native to Excel ( RAND()*(b-a)+a
or
RAND()*100 ) would give me a true random number, or random number from
0-100 for example, but is there any way I could exclude all but
specific
numbers from the value range and have it pick 5 values from my set as I
described?

Hopefully I've described what I'm after well enough that it makes
sense.
Thank you in advance.

KM