Scheduling Students to Activates Based on Student Choices

Hello,

I'm trying to find an easier way to create a schedule for a school event. I was hoping I can use solver to help find an answer to my problem. I'm no pro, so I was wondering if someone can help.

Data Source:
- We have student select choices of activities to do for a day.
- Each student choose 4 activities from a defined list for the day. (10 total to choose from)
- We have a set number of time slots for the activities. (4 Time Slots)
- Not all activities repeat for all time slots.
-Activities have a max number of students per session.

What we are trying to do:
- Create a individual schedule for each student.
- Allows all students to get what activates they choose at some point in the day.
- Get away from manually creating the schedule.

Any help would be greatly appreciated! It will help us save hours of work.

Welcome to the forum.

We don't provide solutions from scratch, but will help and guide you to one.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

This proposal adds eight helper columns to the source data.
Columns F:I are populated using:

Formula:

=IF(AND(SUMPRODUCT(--(F$1:F1=$B2))<=INDEX($U$2:$U$11,MATCH($B2,$T$2:$T$11,0)),COUNTIFS($J2:J2,$B$1)=0),$B2,IF(AND(SUMPRODUCT(--(F$1:F1=$C2))<=INDEX($U$2:$U$11,MATCH($C2,$T$2:$T$11,0)),COUNTIFS($J2:J2,$C$1)=0),$C2,IF(AND(SUMPRODUCT(--(F$1:F1=$D2))<=INDEX($U$2:$U$11,MATCH($D2,$T$2:$T$11,0)),COUNTIFS($J2:J2,$D$1)=0),$D2,IF(AND(SUMPRODUCT(--(F$1:F1=$E2))<=INDEX($U$2:$U$11,MATCH($E2,$T$2:$T$11,0)),COUNTIFS($J2:J2,$E$1)=0),$E2))))

Columns K:N are populated using: =INDEX($B$1:$E$1,MATCH(F2,$B2:$E2,0))
The output is in the form of four Pivot Tables as modeled on the PT's sheet.
Let us know if you have any questions.