EXCEL scheduling bot - random select names from list with conditions

[David Kimball]

Hi Internet,

I need some help with my project. I am trying to set up an excel file that will randomly select a given number of people from a list under the condition that they are scheduled to work.

Example:

Column 1        | Column 2     | Column 3| Column 4    

|

                        | Monday       | Tuesday | Wednesday | Thursday     | Friday

Employee 1    |off                  | off           | 10:00-19:00| 10:00-19:00 |10:00-19:00|

Employee 2    | 10:00-19:00  | off           | 10:00-19:00| off                | 10:00-19:00

..you get the idea

I would like Excel to create a new chart and

- schedule 5 employees randomly selected

- for each day: Mon - Fri

- condition is that they should only appear once and it should not be their day off.

I have gotten to a point where I:

=IF(Table3[Monday]<>"off",VLOOKUP($I24,Table3,2,FALSE),0)

where the vlookup function is based on random numbers generated for the agents and using LARGE function to sort them.

But this also gives me some results where agents that are "off" come up in the result/

please help

[#2]

random is related to ai, there must be a logic in this app (excel)//

[#3]

Hi David,

Your formula

=IF(Table3[Monday]<>"off",VLOOKUP($I23,Table3,2,FALSE),0)

checks first record of the column, and if TRUE lookup the agent. To make it as criteria for entire column you may use

=IFNA(LOOKUP(2,1/(Table3[[Column1]:[Column1]]=$I23)/(Table3[Monday]<>"off"),Table3[[Work Schedule]:[Work Schedule]]),"")

in Monday column and when drag this column to the right to expand on other days.

[#4]

Hi Sergei,

Thank you for the reply - the formula seems to work in column , but the values *agent names* reappear on the subsequent days/columns.

I would like the table to be filled with

- randomly generated agent who is "off" work

- no repeat value throughout table

I do not know how to proceed

[#5]

Hi David,

I added helper part of the table to collect random numbers for agents who are off by weekdays. For Wed formula will be

=AGGREGATE(14,6,Table3[Random]*
(Table3[Wednesday]="off")*
ISNA(MATCH(Table3[Random],[Mon],0))*
ISNA(MATCH(Table3[Random],[Tue],0)),
ROW()-ROW(Table2[[#Headers],[Mon]]))

First filter selects who are off and next two filters - who was selected on previous days.

Main part of table convert above to agent names

=IFNA(INDEX(Table3[[Work Schedule]:[Work Schedule]],MATCH([@Wed],Table3[[Random]:[Random]],0)),"")

Please see attached.