Need formula to randomize employee numbers for history audits

[Cor Bryant]

Hello All,

I'm an IT Director and I'm working on a spreadsheet that will randomiz
employee numbers (1 through 29) so that I can do truly random audits.

In F11, I'm currently using =RANDBETWEEN(1,29) which generates th
random employee number I need when I press the F9 function key. What
want to do now is remove certain numbers from being a result.

Those numbers I want excluded will be listed in J16, J17, J18, J19, J20
J21, J22, J23, J24, J25, J26 and J27.

Each of those locations can contain more than one number seperated by
comma. So J17 may have employee numbers 1, 17, 13 ... like so...

Basically, I'm going to hit F9 three times per month to generat
employee numbers to audit and then I'll enter those results in a "Done
column listed for each month (J16 through J27) and I want those exclude
from future random results.

I'LL ZIP UP THE SPREADSHEET I'VE DONE SO FAR IF IT HELPS TO EXPLAIN WHA
I'M TALKING ABOUT HERE. I'VE ADDED 3 SUPPOSED RESULTS FOR JANUARY,
WOULD NEED THOSE AND ALL CELLS BELOW THAT DOWN TO J27 INCLUDED IN TH
\"EXCLUDE\" PART OF THE FORMULA

Any help would be greatly appreaciated. I am really weak with Excel an
I've been working on that. Just not quite there yet. Thanks guys

+-------------------------------------------------------------------
|Filename: Forum - Hist Audit Wksht.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=311
+-------------------------------------------------------------------

 

[zvkmpw]

I'm an IT Director and I'm working on a spreadsheet that will randomize

employee numbers (1 through 29) so that I can do truly random audits.

Basically, I'm going to hit F9 three times per month to generate
employee numbers to audit and then I'll enter those results in a "Done"
column listed for each month (J16 through J27) and I want those excluded
from future random results.

Any help would be greatly appreaciated.

It sounds like you need to go through the employee list in random order without duplication.

If that’s the objective, I’d suggest a different approach. Make a randomly sorted list once-and-for-all at the outset. Then freeze the list and take the numbers one by one, each time an audit is scheduled.

Here’s one way to make the list.

First, put the employee numbers in A1:A29.

Then put
=RAND()
in B1 and copy down to B29.

Then select columns A:B and sort by column B.

Then delete column B -- it served its purpose.

Remaining in column A is the employee list in random order. You can check off the numbers as they are used to keep track of which was chosen when.

After a list is used up, start over to get a new list randomized differently.

Hope this helps getting started.