Question description:
This user has given permission to use the problem statement for this
blog.
I'm wondering if I can use excel to help me with my problem. I have a list of about 200 people. Each person is going to be assigned to two or three classes. I need to make a schedule for the classes that don't have any conflicts for any of the people. Can I do this?
Solved by E. W. in 52 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
18/09/2018 - 07:41
Hi, welcome to Got it Pro-Excel!
Excelchat Expert
18/09/2018 - 07:41
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
18/09/2018 - 07:42
Thanks! I described what I'm trying to accomplish above. Can this be done on excel?
Excelchat Expert
18/09/2018 - 07:42
Do you have a file that you can send us, so we can evaluate your data and feasibility?
User
18/09/2018 - 07:43
I don't. I'm not sure if I can accomplish what i'm wanting to on excel
Excelchat Expert
18/09/2018 - 07:44
Oh ok, it can be done in Excel but we'll need a data to work with. Do you want for us to create a dummy data?
User
18/09/2018 - 07:44
Yeah that works
Excelchat Expert
18/09/2018 - 07:45
Ok, give me one moment. Let me create a dummy data for you.
Excelchat Expert
18/09/2018 - 07:47
Based on what you’ve shared, you need to assign unique classes to different people and you need to assign remaining classes to people who doesn't have a conflict with it, do you believe that will address your problem?
User
18/09/2018 - 07:48
Yeah
Excelchat Expert
18/09/2018 - 07:49
I'm going to create a dummy data for you first, this may take some time since we are creating it from scratch then start to calculate it from there.
User
18/09/2018 - 07:49
Ok thanks
Excelchat Expert
18/09/2018 - 07:49
But don't worry, we can extend our session for another 20 minutes when our timer hits below 3-minute mark.
Excelchat Expert
18/09/2018 - 07:50
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
18/09/2018 - 07:51
When you say 'conflict', do you mean time conflict?
User
18/09/2018 - 07:51
Yes. Person A can't be in Biology on monday at 8am if he's also supposed to be in history on monday at 8am
User
18/09/2018 - 07:52
So if Person A has to be in Biology and History, only one can be scheduled for Monday at 8am
Excelchat Expert
18/09/2018 - 07:53
Ok, would it help if I create a small scale data, just to show you it's possible?
User
18/09/2018 - 07:53
yes please
Excelchat Expert
18/09/2018 - 07:53
Ok, give me a moment.
Excelchat Expert
18/09/2018 - 07:56
Another question for you - Is the maximum capacity of each unique class only 3?
Excelchat Expert
18/09/2018 - 07:57
For example, Biology 101 can only have 3 students assigned to it?
User
18/09/2018 - 07:57
No each unique class will probably have 15-20 people in it
User
18/09/2018 - 07:57
But each student will only be assigned to 3 classes total each week
Excelchat Expert
18/09/2018 - 07:58
Ok, just a heads up, this may take time to finish. It's 100% possible but this may take more than an hour.
Excelchat Expert
18/09/2018 - 07:58
But, I'll do my best.
User
18/09/2018 - 07:58
ok
Excelchat Expert
18/09/2018 - 08:00
Great, give me a moment. I'm trying to visualize this type of data.
User
18/09/2018 - 08:00
Ok thanks
Excelchat Expert
18/09/2018 - 08:01
To clarify, your end goal is to assign 2-3 classes per student per week? If they have only 1 class per week, you'll need to be able to assign another class that does not conflict other classes, is that correct?
User
18/09/2018 - 08:01
Yes exactly
Excelchat Expert
18/09/2018 - 08:02
Great, thanks!
Excelchat Expert
18/09/2018 - 08:12
Please continue to ask me any questions you have while I’m working.
User
18/09/2018 - 08:13
Ok, I'm just trying to follow along with what you're doing to see what formulas you have helping you
Excelchat Expert
18/09/2018 - 08:13
Ok, great.
Excelchat Expert
18/09/2018 - 08:14
You can extend our session for another 20 minutes one more time when our timer is below 3 minutes.
User
18/09/2018 - 08:14
Ok thanks
Excelchat Expert
18/09/2018 - 08:20
Ok, to show you a very small scale prototype.
Excelchat Expert
18/09/2018 - 08:20
Just to show you different possibilities of Excel.
Excelchat Expert
18/09/2018 - 08:21
I'm done creating a checker of people and classes with conflict.
Excelchat Expert
18/09/2018 - 08:21
Let me first explain the data in 'Classes' sheet.
User
18/09/2018 - 08:21
Ok this looks like it is doing what i wanted
Excelchat Expert
18/09/2018 - 08:21
That's great. I'm well aware that you have a large data but this is just to show you that it's 100% possible.
Excelchat Expert
18/09/2018 - 08:22
So in the 'Classes' sheet.
Excelchat Expert
18/09/2018 - 08:22
We have 3 columns, Class, Schedule and Headcount.
Excelchat Expert
18/09/2018 - 08:22
Class is the actual Class Name.
Excelchat Expert
18/09/2018 - 08:22
Schedule is self explanatory.
Excelchat Expert
18/09/2018 - 08:22
Headcount is the number of students who took up that specific class.
Excelchat Expert
18/09/2018 - 08:23
This is where the People sheet will come in.
Excelchat Expert
18/09/2018 - 08:23
On our 'People' sheet. We have 6 columns.
Excelchat Expert
18/09/2018 - 08:23
Student, Class 1, Class 2, Class 3, # of Classes, Schedule.
Excelchat Expert
18/09/2018 - 08:23
Student is the Student Name.
Excelchat Expert
18/09/2018 - 08:24
Class 1, 2 an 3 are drop-down menu where you can select the class from the Classes sheet.
User
18/09/2018 - 08:24
How did you make them drop down menus with the lass options
Excelchat Expert
18/09/2018 - 08:24
# of Classes is just counting how many classes that specific student already have.
Excelchat Expert
18/09/2018 - 08:24
Sorry, let me finish explaining the last column.
Excelchat Expert
18/09/2018 - 08:25
Schedule is the most vital part here. This is where it will indicate the schedule of the student if it doesn't have conflict. Otherwise, it will show 'Conflict'
Excelchat Expert
18/09/2018 - 08:25
Are you not familiar with Data Validation?
User
18/09/2018 - 08:25
No, i'm not
Excelchat Expert
18/09/2018 - 08:26
Ok, Data Validation option in MS Excel creates this drop-down menu. I'm sorry I cannot explain it in detail since that is not your specific problem.
User
18/09/2018 - 08:26
Ok, no problem
Excelchat Expert
18/09/2018 - 08:27
Now, why don't you try using the drop-down in the 'People' Sheet.
User
18/09/2018 - 08:27
So is there a certain formula you are using to get the results in F Column
Excelchat Expert
18/09/2018 - 08:28
Yes, and it's a long one.
Excelchat Expert
18/09/2018 - 08:28
Try clicking cell F2.
Excelchat Expert
18/09/2018 - 08:28
And you can see in the formula bar the formula that I used.
Excelchat Expert
18/09/2018 - 08:29
Since this is a prototype, you can always simplify that. I just thought on the top of my head what is possible to show you within the time limit.
User
18/09/2018 - 08:29
no that's perfect, thanks!
Excelchat Expert
18/09/2018 - 08:29
That's great, you can keep a copy of this file so you can use it as reference.
User
18/09/2018 - 08:29
Awesome, thanks!
Excelchat Expert
18/09/2018 - 08:30
That's great.
Excelchat Expert
18/09/2018 - 08:30
Would there be anything else I can assist you with regards to the original question and the solution provided?
User
18/09/2018 - 08:31
I don't think so. Thanks so much!
Excelchat Expert
18/09/2018 - 08:31
If that's all, I'd like to wish you have a very nice day ahead of you and we'd love to hear from you again. Feel free to leave a rating and comment at the end of this session. Thank you for using Got it Pro-Excel!
User
18/09/2018 - 08:31
How do i save this for reference?
Excelchat Expert
18/09/2018 - 08:32
To download the file, on our Shared sheet. Go to File > Download As > Microsoft Excel (.xlsx)
User
18/09/2018 - 08:33
Hm i don't think its saving
Excelchat Expert
18/09/2018 - 08:33
Let me send you the file.
User
18/09/2018 - 08:33
oh ok i was able to save it to my drive
User
18/09/2018 - 08:33
thank you!
Excelchat Expert
18/09/2018 - 08:33
Ok, that's great.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
