Scheduling conflict

[Tigerexcel]

The objective is to ensure there are no clashes during the scheduled tests.
Provisional timetable

Mon	Biology
	Maths
	English
Tue	Science
	Geography
	History
Wed	Computers
	Economics
	Art
Thu	Geometry
	Phys Ed
	Finance
Fri	Chemistry
	French
	Accounting
Jack	Biology	Science	Art	French
Fred	Biology	Economics	Geometry	French
Mary	English	Economics	Art	Accounting
Sam	Maths	Art	Finance	Geometry
Errol	English	History	Science	Accounting
Ken	Computers	Art	Finance	English
Sally	Chemistry	Maths	Accounting

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?

 

[GraH]

Hi Tiger,

For the first question, one way of doing a check can is like this:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Day	Exam			Name	Class 1	Class 2	Class 3	Class 4		Mon	Tue	Wed	Thu	Fri
2	Mon	Biology	Mon		Jack	Biology	Science	Art	French		1	1	1		1
3		Maths	Mon		Fred	Biology	Economics	Geometry	French		1		1	1	1
4		English	Mon		Mary	English	Economics	Art	Accounting		1		2		1
5	Tue	Science	Tue		Sam	Maths	Art	Finance	Geometry		1		1	2
6		Geography	Tue		Errol	English	History	Science	Accounting		1	2			1
7		History	Tue		Ken	Computers	Art	Finance	English		1		2	1
8	Wed	Computers	Wed		Sally	Chemistry	Maths	Accounting			1				2
9		Economics	Wed
10		Art	Wed
11	Thu	Geometry	Thu
12		Phys Ed	Thu
13		Finance	Thu
14	Fri	Chemistry	Fri
15		French	Fri
16		Accounting	Fri
Sheet1

Cell Formulas
Range	Formula
K2:O8	K2	=SUMPRODUCT(($B$2:$B$16=$F2:$I2)*($C$2:$C$16=K$1))
C2:C16	C2	=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.

 

[GraH]

So I came this far...

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Day	Exam		Original Exams	Name	Class 1	Class 2	Class 3	Class 4		Mon	Tue	Wed	Thu	Fri		Double Planning Day 1 - Topic 1	Double Planning Day 1 - Topic 2	Double Planning Day 1 - Topic 3	Double Planning Day 2 - Topic 1	Double Planning Day 2 - Topic 2	Double Planning Day 2 - Topic 3
2	Mon	Accounting	Mon	Biology	Jack	Biology	Science	Art	French			1	1		2		Biology	French
3		Maths	Mon		Fred	Biology	Economics	Geometry	French				1	1	2		Biology	French
4		English	Mon		Mary	English	Economics	Art	Accounting		2		2				English	Accounting		Economics	Art
5	Tue	Science	Tue		Sam	Maths	Art	Finance	Geometry		1		1	2			Finance	Geometry
6		Geography	Tue		Errol	English	History	Science	Accounting		2	2					English	Accounting		History	Science
7		History	Tue		Ken	Computers	Art	Finance	English		1		2	1			Computers	Art
8	Wed	Computers	Wed		Sally	Chemistry	Maths	Accounting			2				1		Maths	Accounting
9		Economics	Wed
10		Art	Wed
11	Thu	Geometry	Thu
12		Phys Ed	Thu
13		Finance	Thu
14	Fri	Chemistry	Fri
15		French	Fri
16		Biology	Fri	Accounting
Sheet1

Cell Formulas
Range	Formula
K2:O8	K2	=SUMPRODUCT(($B$2:$B$16=$F2:$I2)*($C$2:$C$16=K$1))
Q2:S8	Q2	=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:V8	T2	=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:C16	C2	=IF(A2<>"",A2,C1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[GraH]

With Power Query, I could do the same a lot faster.

Load the Class Table as connection only

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.

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"

 

[Tigerexcel]

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?

 

[GraH]

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?

 

[Tigerexcel]

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.