Pulling random samples from data

[txstate10]

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!

 

[c_m]

txstate10,
Welcome to the Board.

Do you need those 32 numbers to be unique?

 

[txstate10]

Yeah, they need to be entirely random, no repeating.


Oh, and thanks for the welcome to the board!

 

[c_m]

see:
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

 

[txstate10]

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]

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:

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]

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}