Randomly Select Employees Based on Variables

[samantham]

I am working on a spreadsheet that has over 300 employees listed. These employees are classified as either Clerical, Professional, or Service. We have four events each year with the Big Boss, and need to invite a diverse employee population from these classifications.

"Invited: A" means that the employee was invited and accepted the invitation, "Invited: D" means that the employee was invited and declined the invitation. The dates listed in Column D are the hire date for each employee

Is there a way that I can randomly generate a list of people to be invited (18 total invitations), based on how long it has been since they accepted an invitation, and ensuring that the guests are from a variety of different classifications (we don't want it heavily leaning toward one classification over another).

The table below is just a small sample of the most recent invitation dates... We have invitations dating back almost 10 years.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">330</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">09/08/2010</td><td style="font-weight: bold;text-align: center;;">09/22/2010</td><td style="font-weight: bold;text-align: center;;">10/05/2010</td><td style="font-weight: bold;text-align: center;;">11/30/2010</td><td style="font-weight: bold;text-align: center;;">02/01/2011</td></tr><tr ><td style="color: #161120;text-align: center;">331</td><td style=";">Doe, John</td><td style="text-align: center;;">PAE2</td><td style=";">Professional</td><td style="text-align: center;;">01/01/2001</td><td style="text-align: center;;"></td><td style=";">Invited: D</td><td style=";">Invited: A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">332</td><td style=";">Smith, Sarah</td><td style="text-align: center;;">SS03</td><td style=";">Clerical</td><td style="text-align: center;;">04/05/2007</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style=";">Invited: D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Invited: A</td></tr><tr ><td style="color: #161120;text-align: center;">333</td><td style=";">Miller, Rachel</td><td style="text-align: center;;">SM0H</td><td style=";">Service</td><td style="text-align: center;;">02/03/2002</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Invited: A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">334</td><td style=";">Jones, Robert</td><td style="text-align: center;;">PAE3</td><td style=";">Professional</td><td style="text-align: center;;">09/10/2010</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">335</td><td style=";">Mitchell, Mary</td><td style="text-align: center;;">GS0S</td><td style=";">Service</td><td style="text-align: center;;">07/07/2007</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Invited: D</td><td style=";">Invited: A</td></tr><tr ><td style="color: #161120;text-align: center;">336</td><td style=";">Brown, Bob</td><td style="text-align: center;;">SS01</td><td style=";">Clerical</td><td style="text-align: center;;">06/08/2010</td><td style="text-align: center;;"></td><td style=";">Invited: A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:13.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Staff Lucheons 2010-11</p><br /><br />

 

[Sthrncali]

It sounds like your random list is not so random at all...

UnRandom Criteria One - Select Employees with oldest accepted invitation dates..

UnRandom Criteria Two - Select a variety of position classifications...


All joking aside, based on the information you have provided, it sounds to me like your best bet would be to do the following:

1. Determine the "AVG # of days since last accepted" for each population. (Professional, Clerical, and Service)
2. Identify all employees in each group (Professional, Clerical, Service) with a "# days since last accepted invite" >= the average number of days since last accepted for that respective group.
3. Select 6 random names from that sub-population for each group.

Then you will have 18 names, 6 from each employee type, all of whom haven't been to an event recently..

Does that sound about right?

 

[samantham]

Well, random is the word that was used to describe the guest list to me.

That sounds about right. I'll give it a shot later today... I've been working on other projects, and haven't had a chance to work on this one again yet.