Hello everyone.  I have a list of dorm rooms, 168 to be exact, That I have to schedule room inspections for that have to be completed over the course of 12 months.   168 / 12 = 14 per month.  Now not all of these rooms are gonna be occupied always so I have some criteria that would need to be met during the course of random selection.  Another words, select 14 rooms that are occupied.  The thing is of course, is that there has to be a way to leave the selected rooms alone, not change once selected, when a calculation event takes place.  These being said, now there are154 rooms, took away the initial 14 that were already selected, leaving 154 to be randomly selected from.  Now there are 140 rooms, then 126, 112, 98, 84, 70, 56, 42, 28, 14.  Each time a random selection happens, 14 rooms are taken from the pool of rooms leaving 14 less to be selected from.  Being that the selections are be made based on occupied rooms, I will eventually be left with unoccupied rooms. In the end as people move in and out, an unoccupied room will become occupied and end up getting selected eventually. The over all goal is to have done a room inspection for all occupied room within a 12 month period.  I do not have VBA or Macro access so if anyone can think of a way, using formulas, to make this happen I would greatly appreciate it.  

I need help figuring out how to approach this and if there is a certain function that makes more sense than the Random Selection Function, (But the rooms still have to be randomly selected), I am all ears and ready to take on what ever works to make this happen.

I need your help community.

Thank you,

Carl

​Hello everyone.  I have a list of dorm rooms, 168 to be exact, That I have to schedule room inspections for that have to be completed over the course of 12 months.   168 / 12 = 14 per month.  Now not all of these rooms are gonna be occupied always so I have some criteria that would need to be met during the course of random selection.  Another words, select 14 rooms that are occupied.  The thing is of course, is that there has to be a way to leave the selected rooms alone, not change once selected, when a calculation event takes place.  These being said, now there are154 rooms, took away the initial 14 that were already selected, leaving 154 to be randomly selected from.  Now there are 140 rooms, then 126, 112, 98, 84, 70, 56, 42, 28, 14.  Each time a random selection happens, 14 rooms are taken from the pool of rooms leaving 14 less to be selected from.  Being that the selections are be made based on occupied rooms, I will eventually be left with unoccupied rooms. In the end as people move in and out, an unoccupied room will become occupied and end up getting selected eventually. The over all goal is to have done a room inspection for all occupied room within a 12 month period.  I do not have VBA or Macro access so if anyone can think of a way, using formulas, to make this happen I would greatly appreciate it.   I need help figuring out how to approach this and if there is a certain function that makes more sense than the Random Selection Function, (But the rooms still have to be randomly selected), I am all ears and ready to take on what ever works to make this happen. I need your help community. Thank you, Carl  Read More

​