#StackBounty: #microsoft-excel #worksheet-function Use excel to schedule meetings between 10 people who don't like each other

Bounty: 100

Just joking, we do really.

Lets call the participants Alpha, Beta, Gamma, Delta, Epsilon, Zeta, Eta, Theta, Iota and Kappa.

How do I schedule these meetings so that all of the following are true?

  • Each participant goes to the minimum number of meeting with each other participant. That is, Alpha does not have lots of meetings with Beta and none with Gamma, but has as far as possible an equal number of meetings with everybody.
  • Each participant has one meeting a week, no more no less.
  • There are two meetings of three people, and one of four people.
  • Similar meetings are as spread out as they can be. That is, Gamma and Kappa are not in the same meeting for three weeks running.

I’ve smacked my head on the keyboard for a week trying to satisfy the first 3 criteria, and haven’t even started on the fourth.

There is a link to the file here: https://paste.c-net.org/CreditedHopes

My approach has been:
The meeting 1 field searches the list of possible 3-way meetings, for the first one which matches the three least frequent pair-ups. If N/A, it searches for two, and then for one:

(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=3,0))),
IF([@Fortnight]=1,INDEX(Table1[Trios],1),INDEX(Table1[Trios],MATCH(TRUE,(ISNUMBER(SEARCH(LEFT([@[Least frequent pairs for meeting 1]],(FIND(";",[@[Least frequent pairs for meeting 1]],1)-1)),Table1[All pairs in trio]))+
(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=2,0)))),
IF([@Fortnight]=1,INDEX(Table1[Trios],1),INDEX(Table1[Trios],MATCH(TRUE,(ISNUMBER(SEARCH(LEFT([@[Least frequent pairs for meeting 1]],(FIND(";",[@[Least frequent pairs for meeting 1]],1)-1)),Table1[All pairs in trio]))+
(ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1)),(FIND(";",[@[Least frequent pairs for meeting 1]],1+(FIND(";",[@[Least frequent pairs for meeting 1]],1))))-(FIND(";",[@[Least frequent pairs for meeting 1]],1))-1),Table1[All pairs in trio])))+
ISNUMBER(SEARCH(MID([@[Least frequent pairs for meeting 1]],1+(FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))),
FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE([@[Least frequent pairs for meeting 1]],";",CHAR(1),2))-1),Table1[All pairs in trio]))
)>=1,0))))

The other two meetings formulae are similar, but include a condition not to include any of the people from the same meeting that week. The third meeting searches a seperate list of combinations of four people.

The ‘least frequent pairs formula’ is:

SUBSTITUTE(SUBSTITUTE(TEXTJOIN(";",TRUE,IF(COUNTIF(INDEX([Meeting 1 pair 1],1):INDEX([Meeting 3 pair 6],MATCH([@Fortnight]-1,[Fortnight],0)),Table3[All pairs])=1,Table3[All pairs])),"FALSE;",""),"FALSE","")

Where the ‘meeting # pair #’ cells contain each pair of meeting participants.


Get this bounty!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.