I need to randomize a column of alphanumeric employee ID's for a .

[Guest]

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.

 

[Frank Kabel]

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

 

[JE McGimpsey]

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 here:

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

 

[Guest]

I tried the INDex and get a #name? error

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 here:

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

 

[Frank Kabel]

Hi
do you use a English excel version?

 

[Guest]

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]

Hi
try:
=ROUND(rand()*(4-0)+1,2)

 

[JE McGimpsey]

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

 

[Tushar Mehta]

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

 

[Tushar Mehta]

See Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

--
Regards,

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