#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!!!

#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!!!

#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!!!

#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!!!

#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!!!

#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!!!

#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!!!

#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!!!

#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!!!

#StackBounty: #microsoft-excel #worksheet-function Extract Array Formula from data with multiple criteria

Bounty: 50

I have the following table containing Laptop details

(Laptops)
Name                  |  RAM   |  CPU
===========================================
Laptop Example 1      | 4      | Intel® Core™ i3-8130U   
Laptop Example 2      | 8      | Intel® Core™ i5-8250U 
Laptop Example 3      | 16     | Intel® Core™ i5-8250U
Laptop Example 4      | 8      | Intel® Core™ i7-8550U
Laptop Example 5      | 8      | Intel® Core™ i5-8265U
...

I was looking to create an array using multiple criteria to simply return the name of the laptop. In this case I want to return the name of a laptop with 8 RAM & a CPU containing i5-.

In this table above I would expect to return

(Array)
Name                  
====================== 
Laptop Example 2       
Laptop Example 5      

I haven’t had much experience with array formulas but I have been able to either return all laptop names with 8 RAM

=IFERROR(INDEX(Laptops!A$2:A$500,SMALL(IF(Laptops!$B$2:$B$500=8,ROW(Laptops!A$2:A$500)-ROW(Laptops!A$2)+1),ROWS(Laptops!A$2:A2))),"")

or CPU containing i5-

=IFERROR(INDEX(Laptops!A$2:A$500,SMALL(IF(IFERROR(SEARCH("i5-", Laptops!$C$2:$C$500), 0),ROW(Laptops!A$2:A$500)-ROW(Laptops!A$2)+1),ROWS(Laptops!A$2:A2))),"")

But unable to combine both using the AND function? I have tried…

=IFERROR(INDEX(Laptops!A$2:A$500,SMALL(IF(AND(Laptops!$B$2:$B$500="8",IFERROR(SEARCH("i5-", Laptops!$C$2:$C$500), 0),ROW(Laptops!A$2:A$500)-ROW(Laptops!A$2)+1),ROWS(Laptops!A$2:A2))),""))

However, it does not return anything. Any help would be greatly appreciated.


Get this bounty!!!