Forum Discussion
David Kimball
Apr 06, 2018Copper Contributor
EXCEL scheduling bot - random select names from list with conditions
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 ...
David Kimball
Apr 11, 2018Copper Contributor
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
SergeiBaklan
Apr 11, 2018Diamond Contributor
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.