Scheduling conflict

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
The objective is to ensure there are no clashes during the scheduled tests.
Provisional timetable
MonBiology
Maths
English
TueScience
Geography
History
WedComputers
Economics
Art
ThuGeometry
Phys Ed
Finance
FriChemistry
French
Accounting
JackBiologyScienceArtFrench
FredBiologyEconomicsGeometryFrench
MaryEnglishEconomicsArtAccounting
SamMathsArtFinanceGeometry
ErrolEnglishHistoryScienceAccounting
KenComputersArtFinanceEnglish
SallyChemistryMathsAccounting

The timetable must be created so that no student has more than one exam on any particular day. Looking at the above data, the timetable will have to be changed for Mary as she has Economics and Art on the same day.
Can we use any Excel feature to pick up which student will have an exam clash?
My second question is can a macro or other Excel feature create a clash free timetable given the various student enrolment patterns?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi Tiger,

For the first question, one way of doing a check can is like this:
Book2
ABCDEFGHIJKLMNOPQ
1DayExamNameClass 1Class 2Class 3Class 4MonTueWedThuFri
2MonBiologyMonJackBiologyScienceArtFrench111 1
3MathsMonFredBiologyEconomicsGeometryFrench1 111
4EnglishMonMaryEnglishEconomicsArtAccounting1 2 1
5TueScienceTueSamMathsArtFinanceGeometry1 12 
6GeographyTueErrolEnglishHistoryScienceAccounting12  1
7HistoryTueKenComputersArtFinanceEnglish1 21 
8WedComputersWedSallyChemistryMathsAccounting1   2
9EconomicsWed
10ArtWed
11ThuGeometryThu
12Phys EdThu
13FinanceThu
14FriChemistryFri
15FrenchFri
16AccountingFri
Sheet1
Cell Formulas
RangeFormula
K2:O8K2=SUMPRODUCT(($B$2:$B$16=$F2:$I2)*($C$2:$C$16=K$1))
C2:C16C2=IF(A2<>"",A2,C1)

I'm not very pleased with it, because I would want another formula that indicates me the exams I need to shuffle. Could not figure one out.
Second question is out of my league all together.
 
Upvote 0
So I came this far...
Book2
ABCDEFGHIJKLMNOPQRSTUV
1DayExamOriginal ExamsNameClass 1Class 2Class 3Class 4MonTueWedThuFriDouble Planning Day 1 - Topic 1Double Planning Day 1 - Topic 2Double Planning Day 1 - Topic 3Double Planning Day 2 - Topic 1Double Planning Day 2 - Topic 2Double Planning Day 2 - Topic 3
2MonAccountingMonBiologyJackBiologyScienceArtFrench 11 2BiologyFrench    
3MathsMonFredBiologyEconomicsGeometryFrench  112BiologyFrench    
4EnglishMonMaryEnglishEconomicsArtAccounting2 2  EnglishAccounting EconomicsArt 
5TueScienceTueSamMathsArtFinanceGeometry1 12 FinanceGeometry    
6GeographyTueErrolEnglishHistoryScienceAccounting22   EnglishAccounting HistoryScience 
7HistoryTueKenComputersArtFinanceEnglish1 21 ComputersArt    
8WedComputersWedSallyChemistryMathsAccounting2   1MathsAccounting    
9EconomicsWed
10ArtWed
11ThuGeometryThu
12Phys EdThu
13FinanceThu
14FriChemistryFri
15FrenchFri
16BiologyFriAccounting
Sheet1
Cell Formulas
RangeFormula
K2:O8K2=SUMPRODUCT(($B$2:$B$16=$F2:$I2)*($C$2:$C$16=K$1))
Q2:S8Q2=IFERROR(INDEX($F2:$I2,AGGREGATE(15,6,MATCH(OFFSET($B$1,IFERROR(AGGREGATE(15,6,(COLUMN($K$1:$O$1)-10)/($K2:$O2>1),1),100)*3-2,,3),TRANSPOSE($F2:$I2),0),COLUMNS($Q$1:Q$1))),"")
T2:V8T2=IFERROR(INDEX($F2:$I2,AGGREGATE(15,6,MATCH(OFFSET($B$1,IFERROR(AGGREGATE(15,6,(COLUMN($K$1:$O$1)-10)/($K2:$O2>1),2),100)*3-2,,3),TRANSPOSE($F2:$I2),0),COLUMNS($Q$1:Q$1))),"")
C2:C16C2=IF(A2<>"",A2,C1)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
With Power Query, I could do the same a lot faster.

Load the Class Table as connection only
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="tClasses"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Classes"}, {"Value", "Topics"}})
in
    #"Renamed Columns"

Load the Exams Table, merge with the Class Table and some more.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="tExams"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Exam", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Day"}),
    #"Merged Queries" = Table.NestedJoin(#"Filled Down", {"Exam"}, tClasses, {"Topics"}, "tClasses", JoinKind.LeftOuter),
    #"Expanded tClasses" = Table.ExpandTableColumn(#"Merged Queries", "tClasses", {"Name", "Classes"}, {"Name", "Classes"}),
    #"Grouped Rows" = Table.Group(#"Expanded tClasses", {"Day", "Name"}, {{"Count", each Table.RowCount(_), type number}, {"All", each _, type table [Day=text, Exam=text, Name=text, Classes=text]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 1),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Overbookings", each Table.Column([All],"Exam")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Overbookings", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"All"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Day]), "Day", "Overbookings"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Count"})
in
    #"Removed Columns1"
 
Upvote 0
That's great G. what an amazing resource PQ is. Will save a lot of time. Also nice use of Excel functions.

I guess I'm asking Excel to do too much being a scheduling program, if Excel can't do it, anyone have an alternative?
 
Upvote 0
To a certain distance it would be possible if not all the way.
All I can see as a limit is that at a specific point there must be a conflict to allow all exams being scheduled and all studentes taking the exams for all their classes.
But I expect brilliant minds can make something that considers the constraints to the best level possible. I just don't have the skills to imagine a solution like that.

FYI, in a previous professional life I had a colleague who scheduled the shifts for over 140 agents considering their skill level, the demand flows, the shifts regimes and a bunch of HR rules. It was done through access and VBA. The whole project lead time took almost 2 years before the tool was mature enough. From idea to daily use that is.

A lot is possible also with Excel formulae, VBA, PQ,... I've seen the craziest things being done. Like a Mario Bross game in Excel. Who thinks about that?
 
Upvote 0
To a certain distance it would be possible if not all the way.
All I can see as a limit is that at a specific point there must be a conflict to allow all exams being scheduled and all studentes taking the exams for all their classes.
But I expect brilliant minds can make something that considers the constraints to the best level possible. I just don't have the skills to imagine a solution like that.

FYI, in a previous professional life I had a colleague who scheduled the shifts for over 140 agents considering their skill level, the demand flows, the shifts regimes and a bunch of HR rules. It was done through access and VBA. The whole project lead time took almost 2 years before the tool was mature enough. From idea to daily use that is.

A lot is possible also with Excel formulae, VBA, PQ,... I've seen the craziest things being done. Like a Mario Bross game in Excel. Who thinks about that?
Yeah I've seen people do some remarkable things with Excel and on this forum they come up with such clever solutions, like your PQ.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top