Forum Discussion
Random Selection
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
so as you know I originally suggested a pseudo-random formula that uses a seed and that way you could feed the week# or day# or such to the pseudo function and always get the same output, but changing the seed to the new week/day/other will produce a new random number/sequence. The problem is the 'must be occupied' part since that will also change over time so you would have to keep a logging of WHEN units become occupied and vacant.
The other alternative is to use the built in random generator based on current state of the rooms and then copy/past those results (paste values) into the table. Maybe the easiest is to have a column called [Inspection date] and another called [Inspection Update]. under inspection date will ultimately be the actual date of inspection. and I'm sure you have a column to check if occupied (maybe their name or whatever) but for this we will assume a column called [Occupied] that is true or false. Then=LET(r,RANDARRAY(ROWS( [InspectionDate] ))* [Occupied] *( [InspectionDate] =""), t,INDEX(SORT(r,,-1), 14), o,IF(r>=t,TRUE,""), IF( [InspectionDate] ="",o, [InspectionDate] ))so in line 1 it will create a random array or numbers but zero out the unoccupied and previously inspected rows
in line 2 it will find the Nth largest value, in this example the 14th
in line 3 it will blank out all but the top 14 and replace those with TRUE
and the final output (line 4) will be either the prior inspection date or TRUE if the inspection should be done.
being a RAND function the result will keep changing every time you edit anything on the sheet so when you are ready to committ for that week select that column and COPY then select the [InspectionDate] column and goto Paste->Paste Special->Values Only
Since all the prior dates are repeated it will just add the TRUE to the new locations and those TRUE can be replaced with actual inspection dates. Alternatively you could replace the TRUE in line 3 with WEEKNUM(TODAY()) and then each week you run it and it will just show which weeknum those rooms were selected.
Final caution is that even though rooms that were not occupied will get occupied and have the opportunity to get selected throughout the year this way, you can still have an issue at the end of the year if one or more of those remaining 14 rooms become unoccupied at that point of the year.
48 Replies
A variant if you have access to Python in Excel.
With help from numpy:
#Generate random unique integers from numpy.random import default_rng rng = default_rng() k = xl("k") m=xl("m") numbers = rng.choice(k, k, replace=False) numbers.reshape(m,k//m)- Thank you for your response. I do not have Python in Excel. I opened the workbook but I was not able to experiment with it I was receiving an error.
Some how I must have marked this as Best Response but I cannot validate that as I am not able to test the formula.
Thank you.
Carl
so as you know I originally suggested a pseudo-random formula that uses a seed and that way you could feed the week# or day# or such to the pseudo function and always get the same output, but changing the seed to the new week/day/other will produce a new random number/sequence. The problem is the 'must be occupied' part since that will also change over time so you would have to keep a logging of WHEN units become occupied and vacant.
The other alternative is to use the built in random generator based on current state of the rooms and then copy/past those results (paste values) into the table. Maybe the easiest is to have a column called [Inspection date] and another called [Inspection Update]. under inspection date will ultimately be the actual date of inspection. and I'm sure you have a column to check if occupied (maybe their name or whatever) but for this we will assume a column called [Occupied] that is true or false. Then=LET(r,RANDARRAY(ROWS( [InspectionDate] ))* [Occupied] *( [InspectionDate] =""), t,INDEX(SORT(r,,-1), 14), o,IF(r>=t,TRUE,""), IF( [InspectionDate] ="",o, [InspectionDate] ))so in line 1 it will create a random array or numbers but zero out the unoccupied and previously inspected rows
in line 2 it will find the Nth largest value, in this example the 14th
in line 3 it will blank out all but the top 14 and replace those with TRUE
and the final output (line 4) will be either the prior inspection date or TRUE if the inspection should be done.
being a RAND function the result will keep changing every time you edit anything on the sheet so when you are ready to committ for that week select that column and COPY then select the [InspectionDate] column and goto Paste->Paste Special->Values Only
Since all the prior dates are repeated it will just add the TRUE to the new locations and those TRUE can be replaced with actual inspection dates. Alternatively you could replace the TRUE in line 3 with WEEKNUM(TODAY()) and then each week you run it and it will just show which weeknum those rooms were selected.
Final caution is that even though rooms that were not occupied will get occupied and have the opportunity to get selected throughout the year this way, you can still have an issue at the end of the year if one or more of those remaining 14 rooms become unoccupied at that point of the year.The attachment is the workbook I am working with. I'm sure you've seen parts of this already. As you know, right now at least, it is the Room Inspections Tab I am working on. The left side is what I have now but its the right side I'm trying make work with the formula we have been discussing. There are 3 hidden columns along side each other, columns S, T & U.
I do not completely understand the formula but I am trying to figure it out. As I mentioned in my earlier reply, I really am not sure where to place the formula (I assume in the "ROOM" column) nor what I should put in place if the [OCCUPIED] brackets. If you have a chance could you please make adjustments accordingly to make your formula work. With the formula as it should be, I may get a better grip on what it is doing. The only reason I am hiding the columns is because the wanted layout is that which does not show those columns. With the formula in place and working, I'm sure I'll get a better understanding of what to expect and what to do when the formula produces the results I am looking for. I just realized, the attachment for the layout on the right of the (not yet Table), I did not hide the columns I have mentioned. Obviously no big deal. Also, all the stuff to the right of the Table or Array, is just stuff I was messing with to figure out what I needed to due. It can be deleted.
Thank you,
Carl
Carl_61 I inserted my thought into column AA (colored blue):
=LET(InspectionDate, W4:W171, Occupied, XLOOKUP($T4:$T171,ALL!$C$3:$C$170,ALL!$N$3:$N$170,"")="O", r,RANDARRAY(ROWS( InspectionDate ))* Occupied *( InspectionDate =""), t,INDEX(SORT(r,,-1), 14), o,IF(r>=t,TRUE,""), IF( InspectionDate ="",o, InspectionDate ))For InspectionDate I used column W (you already have there)
For Occupied I used the same formula you used in the 'hidden' or intended to be hidden/removed column and checked if it is "O" but manybe instead should be <>"V" ? I don't know what those other possible entries mean.
This formula creates a column that will be repeat all of the Inspection Dates already entered in column W and then randomly pick 14 of the remaining occupied rows and mark them as TRUE. This column will keep changing and can be 'ignored' (and maybe you want to hide it to not confuse others that go into the sheet?) until you are ready to commit to the next round of inspections. Then you highlight the whole column, copy, then paste - values only - on top of the INSPECTION DATE column. Finally as the inspections for the rooms noted as TRUE in that column are actually performed, the TRUE would be replaced with the actual date the inpection was done. - rinse and repeat next month
Hello Matt and Thank You for your help. I do have some questions about your formula: 1st, does the Inspection data need to be in a table? I am assuming Bracketed [ ] parts of the formula are column headers? The headers/labels for my columns are: NAME, LOCATION, GENDER, BLDG, ROOM, INSPECTION DATE, TIME. The status of [OCCUPIED] is actually on the ALL Tab but could be added to the Room Inspection Tab via =ALL!N3 properly formulated, for instance, off to the side. Which ever would be more practical. What column should this formula be placed into? I assume the Room Column? Because my room numbers are made up of two parts, that is a Wing and the Suite number, I have 2 hidden columns, Columns, F & G, "Wing" Column and "Room#" Column, joined together "=F3&G3" to make up the "ROOM" Column, Column H. I have a Column K that specifies the Month the inspection will fall in but I know that month can change based on room becoming vacant prior to the actual Inspection. Of course, once the Room is selected to be a part of the 14 rooms to be inspected and especially once the inspection has actually been completed, that room has to be out of the Room Pool so it does not get selected again with in a 12 month period. Also, if the room drops out of the selection for the date it was supposed to have been inspected, based on being occupied at the time, the room should go back into the selectable room pool and get picked back up once it becomes re-occupied. Column K has a formula in it. One of which you may have provided. I am going to attach a sample workbook so you can see what I am working with. Right now, all the rooms are showing on the workbook and slated for inspection, even the Vacant ones, falling into a specific month. The idea of course is to select 14 occupied rooms each month and adjust accordingly when a rom becomes Vacant, as we are not needing to inspect rooms that are Vacant. Overall, the intent being to inspect all occupied rooms during a 12 month period. I am sorry if I have repeated myself here and there. By now I'm sure you get what I am trying to achieve. Thank you Carl. I need to work on the workbook so I can attach to a different post.
- Hi Carl,
In response to the questions the references I used in the brackets [] can be table columns or named ranges or just ranges. I just used those as place holders. The important factor is that the 2 ranges [Occupied] and [InspectionDate] have the same dimensionality (i.e. 1 column with the same number of rows). As for that [Occupied] reference I mentioned, "I'm sure you have a column to check if occupied (maybe their name or whatever) but for this we will assume a column called [Occupied] that is true or false." So it could be a column that has their Last Name or is Blank if not occupied and replace [Occupied] with ( [LastName]<>"" )
