# Pulling random samples from data

#### txstate10

##### New Member
I'm given a large group of data, 3000 random numbers. My professor wants me to pull a random sample of 32 from the data. He never covered it in class and all over the internet are complicated tutorials written by people who assume I use excel for a living. I'm looking for a simple answer to the question, how do I pull a random set of 32 values from a large set of 3000 values? please help!

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### c_m

##### Well-known Member
txstate10,
Welcome to the Board.

Do you need those 32 numbers to be unique?

#### txstate10

##### New Member
Yeah, they need to be entirely random, no repeating.

Oh, and thanks for the welcome to the board!

#### txstate10

##### New Member
I appreciate it, but just one last question (I apoligize, when it comes to Excel, I'm a simpleton). Where do I type these, in the "f(x)=" window? Could you clarify their explanation at all?

Generate a static random list with a VBA subroutine
We will (re)use a subroutine we developed above, RandomSelect. What we need is another subroutine that will validate the selection and for valid data call RandomSelect with the correct arguments.

Sub useRandomSelect()
If Not TypeOf Selection Is Range Then _
MsgBox "Please select contiguous cells in a single column": Exit Sub
If Selection.Areas.Count > 1 Then _
MsgBox "Please select contiguous cells in a single column": Exit Sub
If Selection.Columns.Count > 1 Then _
MsgBox "Please select contiguous cells in a single column": Exit Sub
Dim V()
V = Application.WorksheetFunction.Transpose(Selection.Value)
RandomSelect V, Selection.Cells.Count
Selection.Value = Application.WorksheetFunction.Transpose(V)
End Sub</pre>
Generate a static random list with a variant of the RAND and RANK functions
The earlier application of the RAND and RANK functions resulted in a solution that changed each time Excel recalculated the worksheet. This was because the RAND function is "volatile," which means that it returns a new value each time calculations are redone. In the subroutine-based solution below, we use the same RAND and RANK functions except that this time the RAND function is used only temporarily. Once the worksheet recalculates once, the value of the RAND function replaces function itself. Start with the setup in Figure 1 above. Next, select B2:B11 and run the code below. The results in column C will change but will remain unchanged no matter how often one recalculates the worksheet. To get a new set of results in column C, select B2:B11 and rerun the subroutine below.

Public Sub generateRAND()
If Not TypeOf Selection Is Range Then Exit Sub
With Selection
.Formula = "=RAND()"
.Value = .Value
End With
End Sub</pre>

#### c_m

##### Well-known Member
Here is an example:
Lets say A1:A20 contains the data set and you want to generate 5 unique random numbers in B1:B5.

Press Alt+F11. This will open up Visual Basic Editor (VBE). Goto Insert>>Module and Paste this code in the window on right side:

Code:
``````Option Explicit
Function RandomSelection(aRng As Range)
Dim myTarg As Range, _
SrcList, Rslt(), _
i As Long, j As Long, k As Long
Application.Volatile
SrcList = aRng.Value
Set myTarg = Application.Caller
With myTarg
If .Areas.Count > 1 Then
RandomSelection = _
"Function can be used only in a single contiguous range"
Exit Function   '<<<<<
End If
If .Rows.Count > 1 And .Columns.Count > 1 Then
RandomSelection = _
"Selected cells must be in a single row or column"
Exit Function   '<<<<<
End If
If .Cells.Count > aRng.Cells.Count Then
RandomSelection = _
"Range specified as argument must contain more cells than output selection"
Exit Function   '<<<<<
End If
ReDim Rslt(1 To IIf(.Rows.Count > 1, .Rows.Count, .Columns.Count))
End With
j = UBound(SrcList, 1)
For i = LBound(Rslt) To UBound(Rslt)
k = Int(Rnd() * (j - LBound(SrcList, 1) + 1)) + LBound(SrcList, 1)
Rslt(i) = SrcList(k, 1)
SrcList(k, 1) = SrcList(j, 1)
j = j - 1
Next i
If myTarg.Rows.Count > 1 Then
RandomSelection = Application.WorksheetFunction.Transpose(Rslt)
Else
RandomSelection = Rslt
End If
End Function``````
Now close your VBE and Select cells B1:B5. Then press = on your keyboard and paste this:
randomselection(A1:A20)
and then press Control+Shift+Enter (hold control and shift keys together before pressing enter)

#### tusharm

##### MrExcel MVP
Why are you using VBA when you are looking for what you yourself called a "simple answer."

See either of the two sections
Generate a static random subset without repetition -- the Sort method

or

Select a random subset without repetition -- use the Rank function

I appreciate it, but just one last question (I apoligize, when it comes to Excel, I'm a simpleton). Where do I type these, in the "f(x)=" window? Could you clarify their explanation at all?

Generate a static random list with a VBA subroutine
{snip}

Replies
0
Views
475
Replies
0
Views
425
Replies
3
Views
510
Replies
24
Views
3K
Replies
4
Views
844

1,196,022
Messages
6,012,907
Members
441,740
Latest member
Latrs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back