Random number generation from a set of non-sequencial values

[KevinMyers]

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

 

[Harald Staff]

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]

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]

Great. Thanks for the feedback.
Best wishes Harald