Complex task...can Excel do it?

[Ryan Dixon]

I have an organizational task I'm trying to accomplish. Here is the
situation:

At a high school summer camp I have a few hundred students. These
students are of multiple grade levels, from multiple schools, and are
a mix of male and female. For the summer camp we split them into
equal sized groups (or as close to equal as possible) of around 12-15
students.

When we create the groups, we try to:
* Keep the male/female ratios within the groups the same as the
general population of the camp (if out of all the students 75% are
female, we would want all the groups to be 75% female as well, or as
close to it as possible).

* Students from the same school should be not be put together in a
group (if for some reason one school brings a ton of students, each
group should have one student from that school before we start
doubling them up)

* Each group should reflect the general population in the grade level
of the students. If the general population had 10% - 9th grade, 20% -
10th grade, 50% - 11th grade, and 20% - 12th grade, the groups would
all reflect the same percentages in their students.


We've been doing this task manually for as long as I can remember,
which takes forever...but I know it can be computerized. Can anyone
think of a way to get Excel to do this for me automatically?

I appreciate your help!

-Ryan

 

[Traveller]

It's pretty simple, actually.

First, sort your students by the three criteria: Gender first, school
second, and grade level third.

Decide how many groups you will have and assign a letter to each group (for
example, "A" through "J" for 10 groups).

In a separate adjacent column, type the letters in a vertical list, starting
with the first student ("A") through the last letter (such as "J" if you have
10 groups), then replicate this list of letters all the way down to the last
student.

Finally, sort the entire range by the last column, and you are done.

If this isn't clear, I'll try to explain it better.