does any know if sampling from a set can be done until all values are selected?

random wrote on Sun, 25 Apr 2010 13:37:01 -0700:

does any know if sampling from a set can be done until all
values are selected?
One way would be to have a helper column, put randbetween(1, #values) in
it, replace the random numbers with values (paste special), sort and
then use the rest of the values sequentially.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

Certainly:

Sub SelectAllRandomly()
'first row with data to choose from
Const FirstRow = 1
'last row with data to choose from
Const LastRow = 100
'column the data to choose from is in
Const sourceColumn = "B"
'column to put random list into
Const newColumn = "D"
Dim destList As Range
Dim anyDestEntry As Range
Dim newFind As Variant
Dim lookAtRow As Long
Dim goFlag As Boolean
Dim foundFlag As Boolean
Dim movedCount As Long

Do While movedCount < (LastRow - FirstRow + 1)
lookAtRow = Int((LastRow - FirstRow + 1) * Rnd + FirstRow)
newFind = Range(sourceColumn & lookAtRow).Value
'for first time thru the loop
If Not goFlag Then
Range(newColumn & FirstRow) = newFind
movedCount = 1
goFlag = True
Else
Set destList = Range(newColumn & FirstRow & ":" _
& Range(newColumn & Rows.Count).End(xlUp).Address)
'is the item already in the list
foundFlag = False
For Each anyDestEntry In destList
If anyDestEntry = newFind Then
foundFlag = True ' already in list
Exit For
End If
Next
If Not foundFlag Then
Range(newColumn & destList.Rows.Count + 1) = newFind
movedCount = movedCount + 1
End If
End If
Loop
End Sub

Assumes that you'll build the new list on same sheet with the full set list.
To put the routine into your workbook:
First, work with a copy of the workbook just in case!
With the copy open, press [Alt]+[F11] to open the VB Editor and choose
Insert -- Module and copy the code above and paste it into the module.
Change the values for:
Const FirstRow = 1
Const LastRow = 100
Const sourceColumn = "B"
Const newColumn = "D"
as needed for your worksheet setup. Save the workbook.
Run the macro.

"random sampling" wrote:

does any know if sampling from a set can be done until all values are selected?

"James Silverton" wrote:
have a helper column, put randbetween(1, #values) in it

Using RANDBETWEEN incurs a lot of unnecessary overhead and the risk of a
#NAME error and confusion if the user is not familiar with the ATP (before
Excel 2007).

It is sufficient to fill the column with =RAND().

(I am also bothered by the likelihood of duplicate results from RANDBETWEEN
due to quantization. But I can say with impunity that that is a real
problem.)


----- original message -----

"James Silverton" wrote in message
...
random wrote on Sun, 25 Apr 2010 13:37:01 -0700:

does any know if sampling from a set can be done until all
values are selected?
One way would be to have a helper column, put randbetween(1, #values) in
it, replace the random numbers with values (paste special), sort and then
use the rest of the values sequentially.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

Joe wrote on Sun, 25 Apr 2010 14:46:04 -0700:

"James Silverton" wrote:
have a helper column, put randbetween(1, #values) in it

Using RANDBETWEEN incurs a lot of unnecessary overhead and the
risk of a #NAME error and confusion if the user is not
familiar with the ATP (before Excel 2007).

It is sufficient to fill the column with =RAND().

(I am also bothered by the likelihood of duplicate results
from RANDBETWEEN due to quantization. But I can say with
impunity that is a real problem.)

After I posted, I did realize that RAND would be sufficient but surely
anyone who wants to do random selection will have installed the ATP.
Duplicate values (and these can occur even with RAND) do not cause a
problem since all rows will be represented and Sort does not care about
it.


Email, with obvious alterations: not.jim.silverton.at.verizon.not

Material typo....

I wrote:
I am also bothered by the likelihood of duplicate results from RANDBETWEEN
due to quantization. But I can say with impunity that that is a real
problem.

I meant to write: "I can__not__ say with impunity ...". Klunk!


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"James Silverton" wrote:
have a helper column, put randbetween(1, #values) in it

Using RANDBETWEEN incurs a lot of unnecessary overhead and the risk of a
#NAME error and confusion if the user is not familiar with the ATP (before
Excel 2007).

It is sufficient to fill the column with =RAND().

(I am also bothered by the likelihood of duplicate results from
RANDBETWEEN due to quantization. But I can say with impunity that that is
a real problem.)


----- original message -----

"James Silverton" wrote in message
...
random wrote on Sun, 25 Apr 2010 13:37:01 -0700:

does any know if sampling from a set can be done until all
values are selected?
One way would be to have a helper column, put randbetween(1, #values) in
it, replace the random numbers with values (paste special), sort and then
use the rest of the values sequentially.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

Joe wrote on Sun, 25 Apr 2010 15:54:56 -0700:

I wrote:
I am also bothered by the likelihood of duplicate results
from RANDBETWEEN due to quantization. But I can say with
impunity that that is a real problem.

I meant to write: "I can__not__ say with impunity ...". Klunk!

----- original message -----

"Joe User" <joeu2004 wrote in message
...
"James Silverton" wrote:
have a helper column, put randbetween(1, #values) in it

Using RANDBETWEEN incurs a lot of unnecessary overhead and
the risk of a #NAME error and confusion if the user is not
familiar with the ATP (before Excel 2007).

It is sufficient to fill the column with =RAND().

(I am also bothered by the likelihood of duplicate results
from RANDBETWEEN due to quantization. But I can say with
impunity that that is a real problem.)

----- original message -----

"James Silverton" wrote in
message ...
random wrote on Sun, 25 Apr 2010 13:37:01 -0700:

does any know if sampling from a set can be done until all
values are selected?
One way would be to have a helper column, put randbetween(1,
#values) in it, replace the random numbers with values
(paste special), sort and then use the rest of the values
sequentially.


With reference to randomizing the rows of a data set, which is
essentially what I suggested, it might be useful to include another
helper column with integers 1,2,3.....before the random column. so that
you can get the original order back again.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not