I love Friday, as it is time to share more Formula Fun.  Today let’s look at generating some random names against times slots which are going to use to schedule interview times for potential employees.

Download the workbook to follow the example used in the Blog Post.

[wpdm_package id=’10647′]

So, in my scenario today, I have a list of interviewees for a job, and to keep things all fair I can use Excel to generate random time slots during the day of interview for each candidate.

I have already a list of time slots set aside over one day.  Each interview will be scheduled for 45 minutes.  I also have a list of names, who I have chosen to interview based on their CV’s.

I want allocate the names time times randomly.  So we will use a couple of formulas, which are really straightforward to get our interviewees time slotted randomly and fairly.  Now, we are dealing with a really small data set, but this method is useful also for larger data sets as well.

So here is my Excel worksheet with my time slots already suggested, as well as my potential interview candidates.

I have seven time slots and seven candidates. So let’s get to work and randomly assign these people to time slots.

We will use a number of Formulas, RAND, LARGE and also VLOOKUP.  The method I am going to show you will generate random numbers without any duplicates.

Let’s start with RAND().  We are going to use this to generate a random real number between 0 and 1. RAND() does not take any arguments in the formula, just type it into Excel and it will generate you a random number between 0 and 1.   This will be our helper column in the process.  You can see the results below. ( If you keep hitting F9 Excel will continually recalculate the random numbers).

 

Next let’s use the LARGE function with another helper column, to return the 1st to 7th largest numbers in the data set.

=LARGE(B$4:B$10,ROW(A1))

This formula with return the kth largest value in an array.  We can specify the kth value by using the ROW function which will also auto increment as we move down the column. How cool is that?.

See below.  As we drag the formula down the column we select 1st, 2nd, 3rd etc down the column.

So the final piece of the puzzle is to use the fabulous VOLOOKUP formula to look up the Values in Column E and return the time slot associated with it.   Then just drag the formula down to fill all of the time slots.

=VLOOKUP(E4,$B$4:$C$10,2,FALSE)

That’s it. Random time slots. No Repeats.

 

 

Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.