#StackBounty: #microsoft-excel #windows-explorer Excel slow to open second (or subsequent) file from Windows Explorer

Bounty: 50

I use MS Office Home & Business 2019 on Windows 10 PRO and my computer is quite fast (i7 6th gen processor, with 16gb RAM and ssd drive).

I have the following problem when opening Excel files (only when double clicking files from Windows Explorer):

  1. when I open the first file (from Windows Explorer) it opens fast, but only if there was previously no other Excel files open

  2. when I open the second file (from Windows Explorer) it takes up to 10 seconds for the file to open. It doesn’t make a difference how large it is. But on the other hand, if I were to drag&drop the same file from Windows Explorer to the existing Excel it opens fast (or if I open it via Excel–>File–>Open)???

What could be the problem?


Get this bounty!!!

#StackBounty: #microsoft-excel #microsoft-excel-2013 #power-query Cannot connect to OData with Excel 2013 without PowerQuery

Bounty: 50

I need to connect to Odata sources using Excel 2013 in an environment where we are not able to install PowerQuery on most user machines.

Excel 2013 has an option for getting data from Odata feeds, but it doesn’t seem to work at all. I can only get it to work with Power Query.

I am using the Odata.org Northwind demo Odata feed at https://services.odata.org/V2/Northwind/Northwind.svc/

enter image description here

I have tried it with "use the sign-in information for the person opening this file" option, and also with "use this sign-in name and password, trying blank username, "guest", "anonymous"

I always get this error:

enter image description here

Then this one:

enter image description here

If I create a connection to the V3 demo data using powerquery, then disable power query and try to refresh it, I get:

enter image description here

If I create a connection to the V2 demo data using power query, then disable power query and try to refresh it, I get:

enter image description here

I can remember loading OData in excel years ago, but now it seems to be not possible any more? Please advise what I am doing wrong.


Get this bounty!!!

#StackBounty: #microsoft-excel #microsoft-excel-2016 #microsoft-visio #microsoft-visio-2016 Importing Entity Relationships from Excel i…

Bounty: 50

Looking for help/any advice importing a very large many-to-many entity relationship diagram from data in Excel into a relationship diagram in Visio.

My data is identifying upstream & downstream dependency relationships of the entities and I’m looking for a way to visualize this in an easy way from data extracts. Organization hierarchy imports fail as it’s possible for my top level data to rely on more than one underlying upstream dataset. One top-level dataset can have multiple underlying dependencies on mid/low-level datasets, and those mid/low-level datasets can sometimes be summarized into other top-level datasets.

An example would include data structured like thisenter image description here

I had found an article doing essentially what I’m trying to do back from Visio 2002 but I can’t seem to find if the functionality is still supported:
https://boxesandarrows.com/automating-diagrams-with-visio/

The article describes a CSV file formatted as such to create the shapes with numbering schemas to uniquely identify shapes:

Shape,"1.0","","Top A"
Shape,"1.1","","Top B"
Shape,"1.2","","Top C"
Shape,"1.3","","Top D"
Shape,"2.0","","Med A"
Shape,"3.0","","Bot A"
Shape,"3.1","","Bot B"
Shape,"3.2","","Bot C"
Shape,"3.3","","Bot D"

And then appended rows like so to create the links between the shapes using their unique numbering schemas:

Link,"","","","1.0","2.0"
Link,"","","","1.1","2.0"
Link,"","","","2.0","3.0"
Link,"","","","2.0","3.1"
Link,"","","","1.2","3.1"
Link,"","","","1.3","3.2"
Link,"","","","1.3","3.3"

Visio 2016 (even professional version) doesn’t appear to have an easy way to handle data like this. Appreciate any input on how I can get this data in a visual format to better explain some of these relationships.


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