HELP! Trying to match students to preferred roommates

[KTNWINDWARD]

Hi all -

I have a list of 87 students each of whom needs a roommate for an upcoming trip. We asked each student to give us the names of 6 peers who are also on the trip with whom they would like to room. There are two people to a room. The logistics of doing this matchmaking by hand are killing us and we are convinced that someone with greater Excel skill than our team must be able to help us figure out how to let Excel do the matchmaking.

As an example, the list currently looks like this:

Student Name Roommate #1 Roommate #2 Roommate#3 Roommate #4 Roommate #5 Roommate#6

John Smith Frank Grant Josh Patterson Mike Yates Cooper Buck Kevin O'Reily Tyrone Jones
Frank Grant John Smith Josh Patterson Rick Galloway Cooper Buck Kyle Brant Tim Flannery
Tyler Tabb Frank Grant John Smith Chris Jones Mark Smith Kevin O'Reily Tyrone Jones
MarkSmith Steve Simmons Jeremy Plick Mike Yates Cooper Buck Kevin O'Reily Tyrone Jones

Ideally everyone gets someone from their list, but at least one person in each pair should get someone from the list... Any ideas?

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic