Team division: dividing 30 people to 15 pairs according to 4 preferences and their rating

[SnifHaya]

Hi there,

I'm pretty clueless to the wonders of excel. I use some functions (and pick up new ones as I go) but this is way out of my league. I'll explain, kind of hard for me to (shortly) explain in English, so please bear with me:

I'm the head of my local equivalent of the Scout Movement. I have a group of 30 teens (10th and 11th graders) that I need to divide to 15 pairs of guides, each of them given a group of kids between 4th to 8th grade. I've sent them a Google Form asking for their preferences:

1. Preferred pairing from 10th grade
2. Preferred pairing from 11th grade
3. Preferred grade to work with between 4th to 6th grade (the young ones)
4. Preferred grade to work with between 7th to 8th grade (the older ones)

Next, I asked them to rate these four choices from 4 (most important) to 1 (least important). Using these ratings, I was meaning to make sure each ones personal pairing+group to guide would score a certain minimum (to make sure they're pleased)

Now I'm trying to figure how to crunch all this data to create 1 or more possible optimal divisions for me to play with.
After that I would have to play with it as I see fit, but if this imagined magic table could, for instance, prevent me from placing someone in the wrong pair\group by marking it red or something.

Here's an Illustration of what I had in mind:
Screenshot_6.png

Any kind of help would be great
Cheers

[Kaper]

Hi,

I do not think this layout would support automatic selection/suggestion

I'd consider arranging all grades 4th-8th in one table
group 4A
4B
4C
4D
5A
5B
...

(by the way what happens to 9th - you have 10-11 as guides and 4-8 as group members)

Then I'd try to use Solver (a built in Add-on to Excel) to randomize selections, and calculate "a quality" of each selection. Then with solver you can optimize selections to achieve best overall quality. And can set a minimum requirements for a quality of each assignment.

By the way - screenshot is a good for illustration, but real help could be offered if you attach a sample anonimized workbook.

So if the above suggestion is not enough, Would you please attach a sample workbook (not a picture or pasted copy).

(this part is probably not important in your case, but as a general rule)
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.