Timetabling ESOL Classes

[Alan L 185]

Problem:

I’m trying to help my wife who is a teacher of English as Second Language (ESOL) teacher.
After initial English language testing, she places her students in ESOL Groups (Group 1, Group 2 …) of between 1-5 members, depending on their level of English.

She then produces a timetable to bring her student out of their Homeroom classes in their ESOL Group (Group 1, Group 2 …) for English lessons. This has been done on a large hard copy template of the weekly timetable with lots and lots of pieces of paper with student’s names on them.


Attachment:
1. Excel Spreadsheet - to show the different layers of information that my wife needs to consider when developing the timetable for her ESOL Groups.
2. PDF Document - Key Points – Explanation of the Excel Spreadsheet.


What I Need:
I want to provide my wife with a spreadsheet with appropriate formulae that will generate the best fit for ESOL Groups on the timetable for all sessions.

[Richard Buttrey]

OK First points of clarification.
Are you wanting to complete columns O:R of the data sheet as per the note, or produce the information shown on the solution sheet? Or Both?
Maybe the two are the same but it's not clear to me at the moment.

Assuming you want to generate results in columns O:R would you add some typical results and explain how you have worked out the results. The note mentions the concept of 'best fit'. This suggests there might be several possible results for any particular cell which make it vital that we know exactly how you define best fit since any formula will need some sort of rule to work with.

I have questions about the stuff on the Solution sheet but let's understand this first.

[Alan L 185]

It would be great to have a list of all the blocks that each group could fit in to:
e.g.
A1b 1, 3, 4, 5, 6
A2a 1, 2, 5, 6
A2b 1,
A3a 2, 3, 4
A3b 2, 3, 4

To get this information, I've taken each available block and checked which ESOL groups are available based on the availability of each group member.

Once I have the list it would then just be a matter of manually putting each group in the suggested blocks to work out what was the best fit for all the groups.

I don't need the columns O:R filled nor the solution sheet. I just need a list as shown in the example above.

[Richard Buttrey]

Sorry but I don't understand what you expect and importantly the rules you apply.

Your explanation note said that you wanted formulae for columns O:R in the Data sheet along with (I thought) the manually produced TmeTable solution.

Now you appear to be saying you want neither so I'm struggling to understand exactly what you do want, whereany results should appear and how you have arrived at the results explaining how any result relates back to the original data.

[Alan L 185]

Hi, sorry for the confusion. My apology. Please ignore the intention to have formulae to fill in columns O:R.

Since I put up my original post, I think it would now be better (and hopefully easier) to produce a list of all the blocks that each group could fit in to:
e.g.
A1b 1, 3, 5, 6
A2a 1, 2, 5, 6
A2b 1,
A3a 2, 3, 4
A3b 2, 3, 4

To get this information, I've taken each available block and manually checked which ESOL groups are available in that block based on the availability of each group member.

Example:
For block A1b, I checked through the data C3:L24 to see if there were any ESOL students who were already doing something in that block (e.g. David (D6), Seogha (D8) and Eva (D15) have Technology in this block), so this rules them and their ESOL Groups (2 and 4) out. The rest of the groups (1, 3, 5, 6) are available in A1b.

Once I have the list it would then just be a matter of manually putting each group in the suggested blocks to work out what was the best fit for all the groups.

[scottiex]

I think keep in mind the difficulty here is not likely to be so much in the technicality of creating this for you - it is in

1) understanding what you want (and not wanting to spend time creating something that is useless to you).
No-one wants to start working then find you change your requirement, that is not fun for us.

2) getting the info to do that in a clear manner where it is logically clear in a what to do in every situation.

Also, related to (2) think about being able to maintain it (future proofing) - you would want a series of tables (eg simple tables 1 piece of data per cell) of the criteria so you can easily update them - you don't want to be coming back here every time your wife changes something.

[Alan L 185]

I understand what you are saying.

[JeteMc]

When I download the Excel file ESOL Group Timetabler.xlsx and enable editing the extension changes so that the file reads ESOL Group Timetabler.xlsx:2
I can work with the file, and in fact was ready to post a possible solution, however I can not get it to upload again. I can not remove the extra :2 even using "Save as". I am not familiar with what would cause this type of behavior. Perhaps if you could reattach that file (preferably to another post) I could send you a proposed solution.
Let us know if you have any questions.

[JeteMc]

This proposal addresses post #5. This proposed solution employs a helper range of cells that I labeled 'Conflicts'.
'Conflicts' is populated using: =SUMPRODUCT(($M$3:$M$24=$S3)*($C$3:$L$24=T$2))
The 'List' range is populated using: =IFERROR(INDEX($S$3:$S$8,AGGREGATE(15,6,(ROW($3:$8)-2)/(T$3:T$8=0),ROW(1:1))),"")
I copied the Group Data sheet to a new workbook as it is the only sheet on which I did any work.
Let us know if you have any questions.

[Alan L 185]

Hi

Sorry for the delay in my reply. That's interesting about the :2 on the end of the file name. It may have happened when I opened the file in more than one window???

Anyhow, I've attached another copy of the file.

Thanks for the attached file. I'll have a look at it.

[Alan L 185]

Thank you so much JeteMc. You have developed a elegantly simple solution. It is brilliant and will make timetabling so much easier. All the best.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment, if you haven't already, to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post.
By the way your file seems to work now. I hope that you have a blessed day.