Forum Discussion
Random Selection
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.
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.
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]<>"" )