Random pick

[Deborah]

I was just asked to randomly pick about 20% of the rows on
a worksheet containing about 2,000 records.

They don't want the top 20% or highest etc. or I would
have used the filter. They just want a random pick to work
with.

Can anyone help me?

Deborah

 

[Jason Morin]

Put =RAND() in an open column in the first row, copy all
the way down, and sort descending on this column. The
first 400 records are your random selection.

HTH
Jason
Atlanta, GA

 

[sulprobil]

See http://www.sulprobil.com/html/random_numbers.html

Select 20 cells, type =UniqRandInt(2000) CTRL SHIFT ENTER
(array-formula) and use these 20 different random values
as reference to your records.

HTH
sulprobil

PS: With late initialisation the programm could be
improved to (suggestion):


'*******************************************************
**
'Purpose: produce n unique random ints within 1..m, m

= n

'Inputs: mRange - highest possible random number in
1..m
' Implicitly: Length of Application.Caller Range
' (count of requested random numbers)
'Returns: array of unique random integers
'*******************************************************
**
Public Function UniqRandInt(ByVal mRange As Long) As
Variant
'returns n unique random ints within 1..m >= n
'Orig: J.E. McGimpsey
http://www.mcgimpsey.com/excel/randint.html
'Changed by: sulprobil http://Reverse
("moc.liborplus.www")
Dim vArr As Variant
Dim vResult As Variant
Dim nCount As Long
Dim nRand As Long
Dim i As Long
Dim j As Long

Application.Volatile
If TypeName(Application.Caller) <> "Range" Then Exit
Function
With Application.Caller
ReDim vResult(1 To .Rows.Count, 1
To .Columns.Count)
nCount = .Count
If nCount > mRange Then
RandInt = CVErr(xlErrNum)
Exit Function
ElseIf nCount = 1 Then
UniqRandInt = CLng((mRange - 1) * Rnd() + 1)
Exit Function
End If
End With
ReDim vArr(1 To mRange)
nCount = 1
For i = 1 To UBound(vResult, 1)
For j = 1 To UBound(vResult, 2)
nRand = Int(((mRange - nCount + 1) * Rnd) + 1)
If vArr(nRand) = 0 Then
vResult(i, j) = nRand
Else
vResult(i, j) = vArr(nRand)
End If
If vArr(mRange - nCount + 1) = 0 Then
vArr(nRand) = mRange - nCount + 1
Else
vArr(nRand) = vArr(mRange - nCount + 1)
End If
nCount = nCount + 1
Next j
Next i
UniqRandInt = vResult
End Function