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

Close but wrong function. Using ROUND() destroys the uniform nature of
the distribution. Use INT().

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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.

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.