Let's say that we have a data table of courses and people registered to them. The original data can have 120000 of rows and around 100 different courses. Here is a simple data table for clarity.
Thanks to djclements I constructed a table that shows conflicts for each pair of courses.
(Also special thanks to all other people for providing advice and solution to that problem. Fluff, hagia_sofia, Peter_SSs, Domenic and JEC. See post "Lambda to count pairs and output array based on criteria")
And here is the problem: We now want to organize webinars for courses of level A.
As a start, each course could be on a different day so 7 days will be required. This is not essential though.
By noticing the zeros in the table, it is clear that course c2 can be at the same day with c3 or c5 or c6 or c7 but not with all of them because there is conflict, for example between c3 and c5.
But course c2 can be grouped with c3 and c7 because there isn't a conflict for all combinations of two of these.
So we can schedule 4 groups that will need four different times or days to create a program. The order of days does not matter.
1st day: c1
2nd day: c2,c3,c7
3rd day: c5
4th day: c6
As we can see now only four days are required for the webinars.
For purposes of a better distribution we observe that c7 has no conflicts with c5 and can be grouped with that. So we have 2 lessons in each of 2nd and 3rd day instead of 3 and 1 respectively.
So another solution and more preferable would be
1st day: c1
2nd day: c2,c3
3rd day: c5,c7
4th day: c6
As I can see it can not be done in less days, but different combinations can be solutions. For example
1st day: c1
2nd day: c2,c5
3rd day: c3,c7
4th day: c6
If we only have n days available we need to minimize the number of conflicts but with the restriction that no one can have more than two webinars in the same day.
For example if n =3 is stated we can have for example
1st day: c1,c6
2nd day: c2,c3
3rd day: c5,c7
No of conflicts c1,c6 :2
or
1st day: c1,c3
2nd day: c2,c5,c7
3rd day: c6
No of conflicts c1,c3 :1 which is a better choice.
If n is stated as a number greater than 4, the grouping with the 4 days with no conflicts is what we want.
Task 1(mandatory):
Given the table of conflicts and number for available days n, create grouping of courses in n days with the least number of conflicts and a grouping of no conflicts with the least number of days required.
If with the specified n, a person must have more than two webinars in same day report "More days are needed."
Task 2(optional 1):
Try the number of courses of each group to be as close to each other as possible for better distribution.
Task 3(optional 2):
As this is a graph coloring problem, a tree would be a nice addon.
No colors needed though, as this might be too difficult.
Any solution with VBA or lambda functions will be highly appreciated.
Honestly, i didn't come even close to solve it.
Many thanks in advance
Thanks to djclements I constructed a table that shows conflicts for each pair of courses.
(Also special thanks to all other people for providing advice and solution to that problem. Fluff, hagia_sofia, Peter_SSs, Domenic and JEC. See post "Lambda to count pairs and output array based on criteria")
And here is the problem: We now want to organize webinars for courses of level A.
As a start, each course could be on a different day so 7 days will be required. This is not essential though.
By noticing the zeros in the table, it is clear that course c2 can be at the same day with c3 or c5 or c6 or c7 but not with all of them because there is conflict, for example between c3 and c5.
But course c2 can be grouped with c3 and c7 because there isn't a conflict for all combinations of two of these.
So we can schedule 4 groups that will need four different times or days to create a program. The order of days does not matter.
1st day: c1
2nd day: c2,c3,c7
3rd day: c5
4th day: c6
As we can see now only four days are required for the webinars.
For purposes of a better distribution we observe that c7 has no conflicts with c5 and can be grouped with that. So we have 2 lessons in each of 2nd and 3rd day instead of 3 and 1 respectively.
So another solution and more preferable would be
1st day: c1
2nd day: c2,c3
3rd day: c5,c7
4th day: c6
As I can see it can not be done in less days, but different combinations can be solutions. For example
1st day: c1
2nd day: c2,c5
3rd day: c3,c7
4th day: c6
If we only have n days available we need to minimize the number of conflicts but with the restriction that no one can have more than two webinars in the same day.
For example if n =3 is stated we can have for example
1st day: c1,c6
2nd day: c2,c3
3rd day: c5,c7
No of conflicts c1,c6 :2
or
1st day: c1,c3
2nd day: c2,c5,c7
3rd day: c6
No of conflicts c1,c3 :1 which is a better choice.
If n is stated as a number greater than 4, the grouping with the 4 days with no conflicts is what we want.
Task 1(mandatory):
Given the table of conflicts and number for available days n, create grouping of courses in n days with the least number of conflicts and a grouping of no conflicts with the least number of days required.
If with the specified n, a person must have more than two webinars in same day report "More days are needed."
Task 2(optional 1):
Try the number of courses of each group to be as close to each other as possible for better distribution.
Task 3(optional 2):
As this is a graph coloring problem, a tree would be a nice addon.
No colors needed though, as this might be too difficult.
Any solution with VBA or lambda functions will be highly appreciated.
Honestly, i didn't come even close to solve it.
Many thanks in advance
Last edited:
