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

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.

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