I need to pull a random sample of data from very large list of names
Here are the column headers

LAST_NAME GROUP SEQ ENTERED_DATE

I need to randomly pull 15 "GROUP_SEQ" for each last name in the list
Can anyone figure out a way to do this?

Thanks in advance.
Rick

How are your Group_Seq listed? It looks from your example one per name.
Ideally you would pull all seq numbers into an array and randomly select an
index, or 15.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

I need to pull a random sample of data from very large list of names
Here are the column headers

LAST_NAME GROUP SEQ ENTERED_DATE

I need to randomly pull 15 "GROUP_SEQ" for each last name in the list
Can anyone figure out a way to do this?

Thanks in advance.
Rick

Here is how the data is listed

LAST_NAME GROUP SEQ ENTERED_DATE
Jones 6557817 9/14/06 07:45:44 AM
Jones 6559422 9/13/06 10:17:45 AM
Smith 6561183 9/13/06 10:23:44 AM
Smith 6563296 10/2/06 03:07:06 PM
Smith 6564869 9/13/06 10:20:13 AM

The date is not important to the process.
I have over 110k lines split over 2 tabs on the workbook

"John Bundy" wrote:

How are your Group_Seq listed? It looks from your example one per name.
Ideally you would pull all seq numbers into an array and randomly select an
index, or 15.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

I need to pull a random sample of data from very large list of names
Here are the column headers

LAST_NAME GROUP SEQ ENTERED_DATE

I need to randomly pull 15 "GROUP_SEQ" for each last name in the list
Can anyone figure out a way to do this?

Thanks in advance.
Rick

Still confused, what are the rules for assigning a number? In you sample
Jones has 2 numbers, do you need to randomly generate 13 more random 7 digit
numbers or is there a list of them somewhere? How did the numbers beside
their name get chosen? Sorry for all the questions but I would just be
guessing and have to redo it.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

Here is how the data is listed

LAST_NAME GROUP SEQ ENTERED_DATE
Jones 6557817 9/14/06 07:45:44 AM
Jones 6559422 9/13/06 10:17:45 AM
Smith 6561183 9/13/06 10:23:44 AM
Smith 6563296 10/2/06 03:07:06 PM
Smith 6564869 9/13/06 10:20:13 AM

The date is not important to the process.
I have over 110k lines split over 2 tabs on the workbook

"John Bundy" wrote:

How are your Group_Seq listed? It looks from your example one per name.
Ideally you would pull all seq numbers into an array and randomly select an
index, or 15.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

I need to pull a random sample of data from very large list of names
Here are the column headers

LAST_NAME GROUP SEQ ENTERED_DATE

I need to randomly pull 15 "GROUP_SEQ" for each last name in the list
Can anyone figure out a way to do this?

Thanks in advance.
Rick

The GROUP_SEQ is an output from a database here at work, it references to a
Data Entry record in our system, entered by the person with the corresponding
name.
I need to perform an audit on 15 entry records per user
Does that make sense?

"John Bundy" wrote:

Still confused, what are the rules for assigning a number? In you sample
Jones has 2 numbers, do you need to randomly generate 13 more random 7 digit
numbers or is there a list of them somewhere? How did the numbers beside
their name get chosen? Sorry for all the questions but I would just be
guessing and have to redo it.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

Here is how the data is listed

LAST_NAME GROUP SEQ ENTERED_DATE
Jones 6557817 9/14/06 07:45:44 AM
Jones 6559422 9/13/06 10:17:45 AM
Smith 6561183 9/13/06 10:23:44 AM
Smith 6563296 10/2/06 03:07:06 PM
Smith 6564869 9/13/06 10:20:13 AM

The date is not important to the process.
I have over 110k lines split over 2 tabs on the workbook

"John Bundy" wrote:

How are your Group_Seq listed? It looks from your example one per name.
Ideally you would pull all seq numbers into an array and randomly select an
index, or 15.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

I need to pull a random sample of data from very large list of names
Here are the column headers

LAST_NAME GROUP SEQ ENTERED_DATE

I need to randomly pull 15 "GROUP_SEQ" for each last name in the list
Can anyone figure out a way to do this?

Thanks in advance.
Rick

Its big but I wanted to make it easy to customize

Sub main()
Dim myRow As Integer
Dim myArray(10000, 1) As Double
Dim myIndex As Integer
Dim myCounter As Integer
Dim myName As String
Dim myNumber As Integer

myName = "john"
myCounter = 1
myRow = 1
Do Until Cells(myRow, 1) = ""
If Cells(myRow, 1) = myName Then
myArray(myIndex, 1) = Cells(myRow, 2)
myCounter = myCounter + 1
End If
myRow = myRow + 1
myIndex = myIndex + 1
Loop

Randomize

For i = 1 To 15
myNumber = myArray(((myCounter - 0 + 1) * Rnd + 0), 1)
If myNumber = 0 Then i = i - 1
If myNumber < 0 Then

If numstring = "" Then numstring = myNumber Else numstring = numstring & ",
" & myNumber
End If
Next
MsgBox (numstring)

End Sub
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

The GROUP_SEQ is an output from a database here at work, it references to a
Data Entry record in our system, entered by the person with the corresponding
name.
I need to perform an audit on 15 entry records per user
Does that make sense?

"John Bundy" wrote:

Still confused, what are the rules for assigning a number? In you sample
Jones has 2 numbers, do you need to randomly generate 13 more random 7 digit
numbers or is there a list of them somewhere? How did the numbers beside
their name get chosen? Sorry for all the questions but I would just be
guessing and have to redo it.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

Here is how the data is listed

LAST_NAME GROUP SEQ ENTERED_DATE
Jones 6557817 9/14/06 07:45:44 AM
Jones 6559422 9/13/06 10:17:45 AM
Smith 6561183 9/13/06 10:23:44 AM
Smith 6563296 10/2/06 03:07:06 PM
Smith 6564869 9/13/06 10:20:13 AM

The date is not important to the process.
I have over 110k lines split over 2 tabs on the workbook

"John Bundy" wrote:

How are your Group_Seq listed? It looks from your example one per name.
Ideally you would pull all seq numbers into an array and randomly select an
index, or 15.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"RMort" wrote:

I need to pull a random sample of data from very large list of names
Here are the column headers

LAST_NAME GROUP SEQ ENTERED_DATE

I need to randomly pull 15 "GROUP_SEQ" for each last name in the list
Can anyone figure out a way to do this?

Thanks in advance.
Rick

1. put a label in C1, say VALUE
2. in C2 enter
=RAND() and copy all the way down
3. Sort your data by first by LAST_NAME and then by VALUE
4. Copy off the first 15 samples for each name
--
Gary's Student


"RMort" wrote:

I need to pull a random sample of data from very large list of names
Here are the column headers

LAST_NAME GROUP SEQ ENTERED_DATE

I need to randomly pull 15 "GROUP_SEQ" for each last name in the list
Can anyone figure out a way to do this?

Thanks in advance.
Rick