Random Sampling
 

This may be a simple issue and I'm just missing the easy answer but ...
With Excel 2003...I am trying to get a random sample of 50 employees from a
spreadsheet. I've figured out how to do that however I only get the employee
id numbers in my output. I would like my output to contain all the
information on that row for each employee. Is there a way to do that?
Everything I've tried gives me an error message about columns not having a
numeric value.
Thanks

Random Sampling
 

What is your data and what are your formulae?

"Pandorah" wrote:

This may be a simple issue and I'm just missing the easy answer but ...
With Excel 2003...I am trying to get a random sample of 50 employees from a
spreadsheet. I've figured out how to do that however I only get the employee
id numbers in my output. I would like my output to contain all the
information on that row for each employee. Is there a way to do that?
Everything I've tried gives me an error message about columns not having a
numeric value.
Thanks

Random Sampling
 

Hello,

If your employee data resides in Sheet1!A1:J250 (column A:J = 10
column!) then select in Sheet2 cells A1:J50 for example and array-
enter:
=TRANSPOSE(INDEX(Sheet1!A1:J250,vbUniqRandInt(50,2 50),ROW(1:10)))

My UDF vbUniqRandInt you can find at
http://www.sulprobil.com/html/uniqrandint.html

[This example extracts 50 random non-repeating rows with all employee
data (assuming you have 10 entries for each one) from a dataset of 250
employees.]

If you do not want to get new random numbers after hitting F9 again
then please comment-out this line:
'Application.Volatile

Regards,
Bernd

Random Sampling
 

Hi again

Toppers: My sheet is from A1 to J2790. The information on the sheet is ID
numbers, names, dates, and then various "number" columns. Out of 2790
employees I am trying to get a random sample of 50 with all columns included.
I was following the Excel Help instructions on Random Sampling to attempt
this.

Bernd P: I tried your below formula and I seem to be having difficulties.
It tells me it does not recognize it (#NAME?)

=TRANSPOSE(INDEX(Sheet1!A1:J2790,vbUniqRandInt(50, 2790),ROW(1:10)))

Am I typing it wrong? Missing a step maybe? I'm fairly new at using Excel.

Thank you both!!!
:0)

"Pandorah" wrote:

This may be a simple issue and I'm just missing the easy answer but ...
With Excel 2003...I am trying to get a random sample of 50 employees from a
spreadsheet. I've figured out how to do that however I only get the employee
id numbers in my output. I would like my output to contain all the
information on that row for each employee. Is there a way to do that?
Everything I've tried gives me an error message about columns not having a
numeric value.
Thanks

Random Sampling
 

Enter =RAND() in K1 & fill down to K2790. Sort all columns by col K. The
first 50 are your random selection of 50 items.
Bob Umlas

"Pandorah" wrote in message
...
Hi again

Toppers: My sheet is from A1 to J2790. The information on the sheet is
ID
numbers, names, dates, and then various "number" columns. Out of 2790
employees I am trying to get a random sample of 50 with all columns
included.
I was following the Excel Help instructions on Random Sampling to attempt
this.

Bernd P: I tried your below formula and I seem to be having difficulties.
It tells me it does not recognize it (#NAME?)

=TRANSPOSE(INDEX(Sheet1!A1:J2790,vbUniqRandInt(50, 2790),ROW(1:10)))

Am I typing it wrong? Missing a step maybe? I'm fairly new at using
Excel.

Thank you both!!!
:0)

"Pandorah" wrote:

This may be a simple issue and I'm just missing the easy answer but ...
With Excel 2003...I am trying to get a random sample of 50 employees from
a
spreadsheet. I've figured out how to do that however I only get the
employee
id numbers in my output. I would like my output to contain all the
information on that row for each employee. Is there a way to do that?
Everything I've tried gives me an error message about columns not having
a
numeric value.
Thanks

Random Sampling
 

Hello,

First you have to copy my macro code:
1. Press ALT + F11
2. Insert a new module
3. Copy macro code into that module

Then go back to worksheet.
Now
4. Array enter the formula (press CTRL + SHIFT + ENTER to enter
formula, not only ENTER)

Does it work now?

Regards,
Bernd

Random Sampling
 

Bernd, your posting was so helpful. Im trying to do almost exactly what
Pandorah was trying to do; Im trying to randomly select 1094 rows for my
sample out of a population of 6278 rows. The only difference is that my
first row contains labels and Im not sure what the Row(1:10) portion of the
formula is doing. Each of my rows refers to one Peace Corps Volunteer.
Based on your advice on the website, I copied your macro code into a module
and named it vbUniqRandInt. Then I put the formula below into a new Excel
worksheet where I had selected A1:EN1095 and pressed Ctrl+shift+Enter:



=TRANSPOSE(INDEX(Sheet1!A2:EN6278,vbUniqRandInt(10 94,6278),ROW(1:10)))

The end result is #Name? or #N/A fill every cell from A1:EN1095. Could you
help me figure out what Im doing wrong? I would be very grateful!

-Shelley

"Bernd P" wrote:

Hello,

First you have to copy my macro code:
1. Press ALT + F11
2. Insert a new module
3. Copy macro code into that module

Then go back to worksheet.
Now
4. Array enter the formula (press CTRL + SHIFT + ENTER to enter
formula, not only ENTER)

Does it work now?

Regards,
Bernd

Random Sampling
 

Hello Shelley,

You are almost the

Select A2:EN1095 in your second sheet and array-enter:
=TRANSPOSE(INDEX(Sheet1!
A2:EN6278,1+vbUniqRandInt(1094,6277),ROW(1:144)))

Explanation: A1:EN1 will be your title row. It is fix. Do not touch
it. My vbUniqRandInt function has to produce numbers from 2:6278 to
avoid the source title row and to cover all possible rows up to 6278.
So I added 1 to the function's standard output 1:6277 which is exactly
2:6278. Columns A:EN are coded as ROW(1:144). Ok, maybe you should
substitute it by COLUMN(A:EN) which leads to the same result and
explains itself.

WARNING: Please do not name your macro module identical to one of its
macros (the standard macro module names are module1, module2, etc. but
you can press F4 and change them - a common error of beginners is to
give them names which are the same as a macro in them).

Kind regards,
Bernd

PS: The new suggested array-formula:
=TRANSPOSE(INDEX(Sheet1!
A2:EN6278,1+vbUniqRandInt(1094,6277),COLUMN(A:EN)) )