I have 35 students taking 7 classes each. I want to sort them into a random class schedule with no more than 5 kids in a class at the same time. Is there a way to randomly sort them using excel? I've tried the random number generator, but after they are sorted, I haven't been able to limit the results to 5 per class. Any suggestions?
Hello and welcome to the forum,
Have a look a the attachment and tell me if this what you are after.
Steps:
1) Column 1: Repeat Class #1 to 5 (seven times)
2) Column 2: Students #1 to 35
3) Column 3: Ran() (35 times)
4) Sort Columns 2-3 (Column 3 first then COlumn 2)
Done
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
I did that, but now I need each student to be in all 7 classes.
My sheet looks like this. I need each kid in all 7 classes with no more than 5 in a class. Also, I want each class to have a random assortment of kids versus the same group of kids going from class to class together.
Let me know if this what you are after.
1) Each student is in each class (1-7)
2) Classes grouped in groups of five students
3) No student in the same class twice.
abousetta
That is kind of what I am looking for. Attached is the spreadsheet of exactly what I am looking for. 35 student are in column A. Columns B-H are their 7 classes. Each student has all 7 classes. Each column has exactly 5 students per class. It's ok if some of the students have 1 or 2 or even 3 classes together. I just don't want to have every class to be with the same people. This spreadsheet works great for now. I just use find and replace with the actual class name. The problem is, I typed the schedule in by hand. Is there a formula or way to sort so that I can generate this type of schedule no matter how many classes or student I have?
class schedule.xlsx
Last edited by camphelp; 07-02-2012 at 05:03 PM. Reason: typo
This sounds to me like the "Social Golfer Problem" see this link Math Games - Social Golfer Problem
It becomes practically unsolvable with the number of permutation you are looking at.
Maybe I'm looking to deep into your problem.
I think this can be done by using randomizing each student's 7 classes (per row). Since each student can only be one class at one time and there are 35 students then there will have to be seven classes of different 5 different students. I have attempted to do this using formulas but failed due to circular formula errors when using helper cells or recalculation of the results because the RandBetween formula is volatile. My best guess is a User-defined Function will be the most appropriate (or change_event trigger).
Camphelp, would you accept a solution using vba?
abousetta
There is a cleaner way to do this, but I think this is what you want.
column B and C are the formulas you need. for each class I just copied and pasted the values into column.
The classes are 1-7, the grouping is A-G. If anything is unclear, let me know, but I think this works not only for this situation, but any additions or subtractions going forward.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks