Forum Discussion

David Kimball's avatar
David Kimball
Copper Contributor
Apr 06, 2018

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 ...
Formulas & Functions
training
David Kimball's avatar
David Kimball
Copper Contributor
Apr 11, 2018

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's avatar
SergeiBaklan
Diamond Contributor
Apr 11, 2018

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.

Resources