Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to sort al list in random order for sampling? | Excel Discussion (Misc queries) | |||
Random Sampling rows | Excel Discussion (Misc queries) | |||
Random Sampling | Excel Discussion (Misc queries) | |||
how to use excel to do random sampling | Excel Discussion (Misc queries) | |||
Sampling | Excel Discussion (Misc queries) |