Hello All,

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.

In F11, I'm currently using =RANDBETWEEN(1,29) which generates the random employee number I need when I press the F9 function key. What I 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 a comma. So J17 may have employee numbers 1, 17, 13 ... like so...

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.

I'll zip up the spreadsheet I've done so far if it helps to explain what I'm talking about here. I've added 3 supposed results for January, I would need those and all cells below that down to J27 included in the "exclude" part of the formula

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

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.