I need to randomize a column of alphanumeric employee ID's for a .
I have a column of employee id's that have alpha numeric characters I need to
pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
Hi
in B1 put =RAND() copy this down for all rows Now sort with column B and pick from column A the number of required IDs. With resorting you get a new sample -- Regards Frank Kabel Frankfurt, Germany topkick wrote: I have a column of employee id's that have alpha numeric characters I need to pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
One way:
If your values are in A1:A100, enter =RAND() in B1 and copy down to B100. Select a cell in column B and choose Data/Sort, sorting on column B. Take the first N alphanumerics in column A, where N is the number of samples. Alternatively, to do it without sorting: If you enter the RandInt User Defined Function found he http://www.mcgimpsey.com/excel/randint.html If your alphanumerics are in A1:A100, then to pull 15 samples, select B1:B15 and array-enter =INDEX(A:A,RandInt(1,100)) In article , "topkick" wrote: I have a column of employee id's that have alpha numeric characters I need to pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
I tried the INDex and get a #name? error
"JE McGimpsey" wrote: One way: If your values are in A1:A100, enter =RAND() in B1 and copy down to B100. Select a cell in column B and choose Data/Sort, sorting on column B. Take the first N alphanumerics in column A, where N is the number of samples. Alternatively, to do it without sorting: If you enter the RandInt User Defined Function found he http://www.mcgimpsey.com/excel/randint.html If your alphanumerics are in A1:A100, then to pull 15 samples, select B1:B15 and array-enter =INDEX(A:A,RandInt(1,100)) In article , "topkick" wrote: I have a column of employee id's that have alpha numeric characters I need to pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
Hi
do you use a English excel version? -- Regards Frank Kabel Frankfurt, Germany "topkick" schrieb im Newsbeitrag ... I tried the INDex and get a #name? error "JE McGimpsey" wrote: One way: If your values are in A1:A100, enter =RAND() in B1 and copy down to B100. Select a cell in column B and choose Data/Sort, sorting on column B. Take the first N alphanumerics in column A, where N is the number of samples. Alternatively, to do it without sorting: If you enter the RandInt User Defined Function found he http://www.mcgimpsey.com/excel/randint.html If your alphanumerics are in A1:A100, then to pull 15 samples, select B1:B15 and array-enter =INDEX(A:A,RandInt(1,100)) In article , "topkick" wrote: I have a column of employee id's that have alpha numeric characters I need to pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
Hi, sorry to question you on someone else's question, but it's relevant. I
tried your method for rand and I some amount of success. I get the sheet to put out a random number (0 or 1). I also was able to assign this random function to a macro button so I could just click the button. Finally, I learned how to get a number other than 0 or 1. I then tried to use this in conjunction with the vlookup formula. The problem I encountered with this, is vlookup see the random number generated, however that random number is not exact. Example, I have the following "test" sheet: 1 Red 2 Blue 3 Green 4 Orange Now, I have it set to =rand()*(4-0)+1, which will give me a random number, 1-4. When I generate a number, let's say 3, it gives a random number between 3.0000-3.9999 (just for the number 3 of course, it will generate 1.000-1.999, etc.) I tryed to find something in the format cell, but none of the choices in the "Number" tab works. Any help? "Frank Kabel" wrote: Hi in B1 put =RAND() copy this down for all rows Now sort with column B and pick from column A the number of required IDs. With resorting you get a new sample -- Regards Frank Kabel Frankfurt, Germany topkick wrote: I have a column of employee id's that have alpha numeric characters I need to pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
Hi
try: =ROUND(rand()*(4-0)+1,2) -- Regards Frank Kabel Frankfurt, Germany "Shannon W." schrieb im Newsbeitrag ... Hi, sorry to question you on someone else's question, but it's relevant. I tried your method for rand and I some amount of success. I get the sheet to put out a random number (0 or 1). I also was able to assign this random function to a macro button so I could just click the button. Finally, I learned how to get a number other than 0 or 1. I then tried to use this in conjunction with the vlookup formula. The problem I encountered with this, is vlookup see the random number generated, however that random number is not exact. Example, I have the following "test" sheet: 1 Red 2 Blue 3 Green 4 Orange Now, I have it set to =rand()*(4-0)+1, which will give me a random number, 1-4. When I generate a number, let's say 3, it gives a random number between 3.0000-3.9999 (just for the number 3 of course, it will generate 1.000-1.999, etc.) I tryed to find something in the format cell, but none of the choices in the "Number" tab works. Any help? "Frank Kabel" wrote: Hi in B1 put =RAND() copy this down for all rows Now sort with column B and pick from column A the number of required IDs. With resorting you get a new sample -- Regards Frank Kabel Frankfurt, Germany topkick wrote: I have a column of employee id's that have alpha numeric characters I need to pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
Did you put RandInt in a regular code module?
If you're unfamiliar with UDF's see David McRitchie's "Getting Started with Macros and User Defined Functions": http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , "topkick" wrote: I tried the INDex and get a #name? error |
See Random Selection
http://www.tushar-mehta.com/excel/ne...ion/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have a column of employee id's that have alpha numeric characters I need to pull a random sampling for a drug test how do I do it? I tryed rand() and Randbetween and no luck making that work. |
All times are GMT +1. The time now is 05:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com