Sleeper: Dodge-Ball-Team Sorting System

[sti003]

Hey everyone!:mkay

I'm sort of in a pinch to solve this problem, so I'm reposting...

I'm living out in Japan, and was given the wonderful task of organizing my kid's elementary school's dodgeball tournament. Can anyone help me with the following problem?:dunno

What I have to do, is take a list of kids (15 in grade 3, 12 in grade 4), and sort them into a number of teams (the number can be inputted at the top of the worksheet), and then shuffle the teams each day over a number of days (this number can also be inputted at the top of the worksheet).

The restrictions being, that if possible, the kids can only be on the same team only once (to get the kids playing with as many different other kids as possible)...so if Kid A and Kid B are on the same team Day 1, they will not be on the same team day ever again (Day 2 - 4). AND if they are from the same family (have the same last name), they should only be on the same team if it is unavoidable.

I was trying to create a macro that loops through the list of kids, and puts them each on a team until all the kids run out (that way, the teams are balanced number wise as best as possible) on Day 1 (in a new worksheet), then shuffle the names for Day 2, Day 3, Day 4, etc...and check to make sure that in the past that 2 kids had never played together...however, I'm completely lost now.:banghead:

My original macro/Procedure was really spaghetti-like, so I'm open to throw it out if anyone has any better alternatives. :hi:

Please help me!

Umm...I think I attached some sample data...but I can't see that it is attached...if anyone wants the data, let me know & I'll send it to you directly!

Thanks again!

Dave

[xCav8r]

Sounds interesting. I'll give it a wack. Send me the data, and I'll attach it for you too.

[xCav8r]

This might be easier to do in Access, but here are my initial thoughts anyway.


Loop through the list of kids.

Loop (nested) through each day.

Loop (nested) through each team.

Loop (nested) through the members of the team. For each member, compare last names. If same, augment unique team counter by two.
If there are previous days, loop (nested at same level as previous) through all the teams (more nested loops) for as many days as necessary, augmenting the counter by one for every previous pairing.
Now, determine which counter has the lowest value and place the kid on that team. In case of a tie, place the kid on the first team with the lowest value.




Some serious looping. Perhaps someone might suggest a better approach.

[sti003]

If Access is easier, then I have NO problem with using that either!:thumb

I'm just slightly more useless in Access than Excel :bug: , so I thought Excel might be the way to go....BUT, I would totalyl be cool with Access too! :bow:

BTW...your ideas on looping seem pretty sweet...

Dave

[xCav8r]

Oh, I forgot to take into account the maximum capacity of a team. If the max is already reached, then the counter should be augmented to something that will prevent the kid from being placed on that team.

We'll stick with Excel if that's what you know better.

[xCav8r]

I don't have time atm to work on this. Would anyone else be willing to take a stab at it? Or, Dave, have you tried my loop-dee loop suggestion?

[sti003]

X,

I didn't get the chance to try it last night. I'll see what I can do on the weekend, but if there is anyone else out there willing to help as well, I'm very appreciative!

Dave

[sheeeng]

I have to apologize to sti003 for not helping out.
Currently i'm still had many homework...
Sorry...