Prev: last negative cell
Next: TextBox lock
From: random sampling random sampling on 25 Apr 2010 16:37
does any know if sampling from a set can be done until all values are selected?
From: James Silverton on 25 Apr 2010 17:15
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

From: JLatham on 25 Apr 2010 17:16
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?
From: Joe User on 25 Apr 2010 17:46
"James Silverton" <not.jim.silverton(a)verizon.net> 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" <not.jim.silverton(a)verizon.net> wrote in message
news:uMb6$xL5KHA.5808(a)TK2MSFTNGP02.phx.gbl...
> 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

From: James Silverton on 25 Apr 2010 18:27
Joe wrote on Sun, 25 Apr 2010 14:46:04 -0700:

> "James Silverton" <not.jim.silverton(a)verizon.net> 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

 |  Next  |  Last
Pages: 1 2
Prev: last negative cell
Next: TextBox lock