Timetabling

I wonder if anyone can help I have a spreadsheet with a list of students and their choices for a level. Each subject has been assigned a number. What I want is to be able to block the subjects into 4 blocks so that all the students fit into these 4 blocks and I can find out how many students would be doing the subject in each of those blocks. I have attached a sample spreadsheet. Any ideas would be greatfully received. PLease let me know if you would like any further information

Many thanks

Jack

One thing I forgot to add is that I would like as few subjects as possible in each of the blocks. Hope someone can at least shed some light on it

Many Thanks

Jack

I'm a little confused as to what exactly you're trying to do. Do Choices A-D mean that these students will be put into 1 block only and that A is first choice, B is second, C is third, and D is fourth? Or do all choices have to be accommodated in different blocks if possible?
Could you perhaps walk through an example of what you want using student A? Thanks.

Each student has to make 4 choices of subjects out of 10 so student A has chosen subjects 1,2,4 and 9. All students can choose any combination of 4. I then have to try and fits these into blocks for teaching. So if there was only one student then in Block A would be subject 1, in Block B would be 2 in Block C would be 4 and in Block D would be 9.

If someone else chooses 1,2,4,and 8 then Block D would also have to have 8 in it as well.

If someone else chooses 2,4,7,9 then block A would have 7 in it as 2,4,9 are already in Blocks B,C and D.

Does this make sense? I want to try and keep the number of subjects in each block to a minimum while accomodating everybodys choices.

I know this is very tricky but any help even somewhere to start would be a great help.

Many thanks


Jack

One thing I forgot to add is a subject can be in more than one block if needed.

There's no way I can think of to do this automatically. Perhaps one of the moderators on here can figure out an automatic solution. The most concise way to guarantee that every student can have their requests met is to teach classes 1-6 in Block A, 2-7 in Block B, 3-8 in Block C, and 4-9 in Block D. The problem is that this is not necessarily the optimal solution, as there could be unnecessary blocks, but without knowing every possible concatenation, you would have to use a brute force approach to find the answer. Sorry I can't be of more help, but with an indeterminate number of students, I don't know how you would find the answer with formulas.

Thank you for your help. Yeah that is about as far as I got. There must be some way of doing it but no idea how!

To take it one step further, an easy way to cut one or two classes could be to take the Min and Max values of students' choices. If the lowest Max value is 5, for example, then class 4 would not have to be taught in the last block because all other possible combinations could be taken care of without it there. Likewise, if 5 is the highest MIN value, then class 6 would not have to be taught in block A. I'll think about it a little tonight if I get the chance, but with 126 possible concatenations, it seems unlikely that there'll be an easy solution. If you have more than double that number of students, it becomes unlikely you'll be able to trim more than 1 or 2 courses, if any, from that plan.

Yeah there will be more students but some of the courses will be a lot more popular than others. Thank you again for all your help

Jack

No problem. I had a thought on my way home that could lead to some automation of this. Try the attached spreadsheet. If all students have different choices for their four classes (on row 15, 9 is chosen twice), then the table at the bottom should tell you what classes you do need in each block. It's not guaranteed to be perfect, but with a large number of students, it should be pretty efficient.

Thank you that looks great the next thing I would need to do is to say how many there are in each class e.g. how many are doing subject 2 in block 1 and how many in block 2.

I would then need to be able to say that if there is more than 15 then they need a new group in another block.

Sorry for adding all these complications but when it is done it will save lots and lots of hours work

Many thanks for all your help

Jack

Instead of the IF(COUNTIF formula I have now in the bottom table, change it to just COUNTIF, cutting out the conditions and ,"Yes","No"). This will give you the count. It will give the added benefit of seeing if there is only one or two students in a given class, so you can see if their class choices allows for that class to be taken in a different block.

Yeah I got that far is there any way anyone can think of splitting them if there are more than 15 in class in one block and then the final step of saying what each student is doing in each block. I know this is very tricky so any help would be gratefully received.

Many thanks

Jack

Thank you for everybodys help does anybody have any other ideas of how I could do this?

Many thanks Jack