Random Sampling

[Pandorah]

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

 

[Toppers]

What is your data and what are your formulae?

 

[Bernd P]

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,250),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

 

[Pandorah]

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)

 

[Bob Umlas]

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

 

[Bernd P]

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

 

[Shelley]

Bernd, your posting was so helpful. I’m trying to do almost exactly what
Pandorah was trying to do; I’m 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 I’m 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(1094,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 I’m doing wrong? I would be very grateful!

-Shelley

 

[Bernd P]

Hello Shelley,

You are almost there:

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)))