Random Number - Statistical Sample Question

[Mopacs]

Hello again,

Another day, another question.....(i'm editing this post with a more concise explanation of what I intend to do..3/25):

I run a periodic "random sample" report for staff here at work. The staff has a set of providers they monitor. They require a random sampling of those provider's clients (usually between 10 and 150 clients per provider). Each client has a ID# assigned to them...from those I generate the sample.

In Column "A" I have a listing of unique client numbers (the number of client numbers will vary every time). The list always starts in cell "A17"..but that is pretty much the only constant here. The number of numbers that start at A17 can be anywhere from 10 to 150..if not more (ie, go up to cell A167 or more).

What I want to do is have a macro (or function?) dynamically select all 'filled' cells from A17 until the end of that list.. then randomly select 25% of those client numbers (ie, if there are 100 client numbers, it will randomly select 25 unique client numbers from that list), and then have the macro place those numbers into a seperate area of the worksheet (starting at cell C17).

I hope I'm not too long-winded, but any help here would be greatly appreciated! If anyone else has any suggestions here, please don't hesitate to respond.

Thanks in advance,

Rob

[ This Message was edited by: Mopacs on 2002-03-25 11:06 ]

[Mark W.]

Take a look at my response at...

http://www.mrexcel.com/board/viewtop...c=2254&forum=2

[Mopacs]

On 2002-03-20 14:17, Mark W. wrote:
Take a look at my response at...

http://www.mrexcel.com/board/viewtop...c=2254&forum=2

Wonderful.. that posting is very relevant to my question. I will try and apply your suggestions to my project in the morning. If anyone has additional suggestions, those are welcome too!

Thanks again,

Rob

[Joe Was]

This code picks one random name from a list of names. It can be modified to do your %'s.

Sub myRnd()
'Find a random name in a existing names list.
'By Joe Was, 6/27/2001.

Dim myOrder As Range
Dim myName
Dim mySelect As Variant
Randomize
'Note: The 20 below = the ending ROW of your names list.
' The 1 below = the starting ROW of your names list.
mySelect = Int((20 * Rnd) + 1)

'Note: The "A" below is the column where your names list is.
myName = Range("A" & mySelect)

'Put the answer in a cell
Worksheets("Sheet1").Range("C1") = myName

'Put the answer in a screen message box.
MsgBox "The selection is:" & Chr(13) & Chr(13) & myName
End Sub

You can comment out the MsgBox code, its an option! Hope this gets you started. JSW

[Mopacs]

On 2002-03-20 14:31, Joe Was wrote:
This code picks one random name from a list of names. It can be modified to do your %'s.

Sub myRnd()
'Find a random name in a existing names list.
'By Joe Was, 6/27/2001.

Dim myOrder As Range
Dim myName
Dim mySelect As Variant
Randomize
'Note: The 20 below = the ending ROW of your names list.
' The 1 below = the starting ROW of your names list.
mySelect = Int((20 * Rnd) + 1)

'Note: The "A" below is the column where your names list is.
myName = Range("A" & mySelect)

'Put the answer in a cell
Worksheets("Sheet1").Range("C1") = myName

'Put the answer in a screen message box.
MsgBox "The selection is:" & Chr(13) & Chr(13) & myName
End Sub

You can comment out the MsgBox code, its an option! Hope this gets you started. JSW

Hey Joe,

Thanks for responding. Well I'm having a little trouble implementing your code. Let me see if I understand correctly. You are pointing to a list of numbers in a particular column (A?) and then specifying the set number of rows it will select from in column A? Then it selects one number at random from that list, and places that number in a specific cell?

You'll have to excuse my confusion! I believe I understand what you are trying to do..though I may be WAY off!

Anyways, to give you (or anyone else who reads this) a more specific example of what I am doing, it is this:

In Column "A" I have a listing of unique client numbers (the number of client numbers will vary every time). The list always starts in cell "A17"..but that is pretty much the only constant here. The number of numbers that start at A17 can be anywhere from 10 to 150..if not more (ie, go up to cell A167 or more).

What I want to do is have the macro (or function?) dynamically select all 'filled' cells from A17 until the end of that list.. then randomly select 25% of those client numbers (ie, if there are 100 client numbers, it will randomly select 25 unique client numbers from that list), and then have the macro place those numbers into a seperate area of the worksheet (starting at cell C17).

I hope I'm not too long-winded, but any help here would be greatly appreciated! If anyone else has any suggestions here, please don't hesitate to respond either.

Thank You,

Rob

[Ian Mac]

You could possibly Adapt an answer I gave for a Random League Generator:

check:

http://mrexcel.com/board/viewtopic.p...c=1648&forum=2

[Mopacs]

On 2002-03-25 11:09, Ian Mac wrote:
You could possibly Adapt an answer I gave for a Random League Generator:

check:

http://mrexcel.com/board/viewtopic.p...c=1648&forum=2

Hmm... unfortunately I cannot seem to apply these examples to my 'problem'. I'm sure I"m missing a step or two. I suppose the dynamic number of clients to sample is causing a problm here too.. as they vary every time. From what I can see, the "Rand" or "Randbetween" functions return a single number?

Any further assistance here would be greatly appreciated.

Thanks,

Rob

[Ian Mac]

if you want me to e-mail an eg I'd be happy to, as long as I've got exactly what you need, the dynamic range won't make a diference.

BUT do you want the percentage/Number of people to round up or down i.e. do you want 30% of 10 to be 3 or 4

I'll have to do this at home as I'm just about to leave work

[Mopacs]

On 2002-03-25 12:05, Ian Mac wrote:
if you want me to e-mail an eg I'd be happy to, as long as I've got exactly what you need, the dynamic range won't make a diference.

BUT do you want the percentage/Number of people to round up or down i.e. do you want 30% of 10 to be 3 or 4

I'll have to do this at home as I'm just about to leave work

That would be great,

Dont go out of your way to do this either.. . I'll email you some of the specifics. Dont necessarily have to use a percentage either. I will usually know the number to use.. actually now that I think of it, it would be best to use a set, round number (which will be determined ahead of time) (ie, 6), and then generate 6 random, unique numbers from a list of 24 numbers, etc. From there the 6 randomly selected numbers will paste into a specified column, starting at cell C17.

Thanks again,

Rob

[Tom Schreiner]

Hi
Paste this code in a standard module and run off of a button or from the macro menu.
Will provide 25% of total client picked randomly with no repeats and list alphabetically in row C

Place this formula in Cell AA1
=counta(AA17:AA1016)
This is to track number of clients

Let me know

Change all the references to sheet1 or change your sheet name to sheet1

Sub MopacsRandomizer()
Dim ArrayCntr As Integer
Dim ArrayUpperBound As Integer
Dim NumClients As Integer
Dim NumberKeeper()
Dim ThisRndNum As Integer
Dim AlreadyGotTheNumberBabeeee As Integer
Dim NumberKeeperCntr As Integer
Dim PutEmInRowC As Long

PutEmInRowC = 16
NumClients = Range("AA1").Value
ArrayUpperBound = Abs(NumClients / 4) + 1
ReDim NumberKeeper(1 To 1)

Do Until UBound(NumberKeeper, 1) = ArrayUpperBound
NumClients = NumClients - 1
ThisRndNum = Int(Range("AA1").Value * Rnd) + 16
AlreadyGotTheNumberBabeeee = False
For ArrayCntr = 1 To UBound(NumberKeeper, 1)
If NumberKeeper(ArrayCntr) = ThisRndNum Then
AlreadyGotTheNumberBabeeee = True
Exit For
End If
Next
If AlreadyGotTheNumberBabeeee = False Then
NumberKeeperCntr = NumberKeeperCntr + 1
ReDim Preserve NumberKeeper(1 To NumberKeeperCntr)
NumberKeeper(NumberKeeperCntr) = ThisRndNum
End If
If NumClients = 17 Then NumClients = Range("AA1").Value
Loop
PutEmInRowC = 16
For ArrayCntr = 1 To NumberKeeperCntr
PutEmInRowC = PutEmInRowC + 1
Sheet1.Range("C" & PutEmInRowC).Value = _
Sheet1.Range("A" & NumberKeeper(ArrayCntr)).Value
Next


Sheet1.Range("C17:C1016").Sort Key1:=Sheet1.Range("C17"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A17").Select
End Sub




[ This Message was edited by: TsTom on 2002-03-25 12:42 ]