Picking a random number from data
 

I have a series of data in rows and columns in excel 5x10 and I would like to
randomly pick 5 numbers from this data. Can this be done? If so how?

Picking a random number from data
 

I have a series of data in rows and columns in excel 5x10
and I would like to randomly pick 5 numbers from this data.
Can this be done? If so how?

You can use the function below my signature to get what you are asking
about. Pass in a range covering the cells holding your data and the number
of values you want randomly returned from that range and the function will
return a Variant containing an array with those values. Here is a sample
code snippet (using an example CommandButton to kick everything off with)
showing this function in use (I used Selection for the range, but you should
supply your actual range in its place)...

Private Sub CommandButton1_Click()
Dim X As Long
Dim Msg As String
Dim Result As Variant
Result = RandomPick(Selection, 5)
For X = LBound(Result) To UBound(Result)
Msg = Msg & Result(X) & vbCrLf
Next
MsgBox Msg
End Sub

I didn't add any error checking, so you should consider doing so. At a
minimum, you'll want to make sure more items weren't requested than cells in
the range.

Rick


Function RandomPick(Rng As Range, HowMany As Long) As Variant()
Dim R As Range
Dim X As Long
Dim RandomIndex As Long
Dim TempElement As Variant
Dim Values() As Variant
Dim Answer() As Variant
Static RanBefore As Boolean
If Not RanBefore Then
RanBefore = True
Randomize
End If
ReDim Values(0 To Rng.Count - 1)
For Each R In Rng
Values(X) = R.Value
X = X + 1
Next
If VarType(Values) = vbArray Then
For X = UBound(Values) To LBound(Values) Step -1
RandomIndex = Int((X - LBound(Values) + 1) * _
Rnd + LBound(Values))
TempElement = Values(RandomIndex)
Values(RandomIndex) = Values(X)
Values(X) = TempElement
Next
End If
ReDim Answer(0 To HowMany)
For X = 0 To HowMany - 1
Answer(X) = Values(X)
Next
RandomPick = Answer
End Function

Picking a random number from data
 

I am somewhat of a novice to excel. Is this some sort of programming
language? How do I input this?

"Winston Williams" wrote:

I have a series of data in rows and columns in excel 5x10 and I would like to
randomly pick 5 numbers from this data. Can this be done? If so how?

Picking a random number from data
 

I am somewhat of a novice to excel. Is this some sort of
programming language? How do I input this?

You posted in the 'programming' newsgroup, so I figured you were writing a
macro. If you are not writing a macro, how were you planning on picking the
numbers? By that, I mean where were you going to put them and/or how were
you going to use them?

Rick

Picking a random number from data
 

I was just going to put them adjacent to the raw data, but I guess I could
also put the on another sheet within the workbook.

"Rick Rothstein (MVP - VB)" wrote:

I am somewhat of a novice to excel. Is this some sort of
programming language? How do I input this?

You posted in the 'programming' newsgroup, so I figured you were writing a
macro. If you are not writing a macro, how were you planning on picking the
numbers? By that, I mean where were you going to put them and/or how were
you going to use them?

Rick

Picking a random number from data
 

Assume you 5 rows by 10 columns starts in A1

in K1:
=Offset($A$1,trunc(rand()*5),trunc(rand()*10))

Place the formula in 4 more cells.

this doesn't guarantee there will be no duplicates.

To check that:
Assume the formula is in K1:K5
in L1

=countif($K$1:$K$5,K1) then drag fill down to L5. Hit F9 until all of
these show 1.

--
Regards,
Tom Ogilvy




"Winston Williams" wrote in
message ...
I was just going to put them adjacent to the raw data, but I guess I could
also put the on another sheet within the workbook.

"Rick Rothstein (MVP - VB)" wrote:

I am somewhat of a novice to excel. Is this some sort of
programming language? How do I input this?

You posted in the 'programming' newsgroup, so I figured you were writing
a
macro. If you are not writing a macro, how were you planning on picking
the
numbers? By that, I mean where were you going to put them and/or how were
you going to use them?

Rick

Picking a random number from data
 

Assume you 5 rows by 10 columns starts in A1

in K1:
=Offset($A$1,trunc(rand()*5),trunc(rand()*10))

Place the formula in 4 more cells.

this doesn't guarantee there will be no duplicates.

To check that:
Assume the formula is in K1:K5
in L1

=countif($K$1:$K$5,K1) then drag fill down to L5. Hit F9 until all of
these show 1.

Not sure if the OP is coming back to this thread or not... he got an answer
over in m.p.e.misc that he seems to like.

Rick

Picking a random number from data
 

Hello Winston,

Select 5 adjacent cells and array-enter
=Random_Pick(A1:E10)
(enter with CTRL + SHIFT + ENTER, not only with ENTER)
if your source data is A1:E10.

My UDF Random_Pick you can get he
http://www.sulprobil.com/html/uniqrandint.html

You can include the macro as follows:
Press ALT + F11.
Insert a module.
Copy macro code from my website into your new module
(do not forget to include VBUniqRandInt code, too).
Go back to your worksheet.

Regards,
Bernd