Mentor Mentee Matching Formula or Macro

[jchrisphonte]

Hello All,

It has been a while since I've posted here... I've been working on a project to help automate an inefficiency in pairing mentees with mentors at my firm...

Here's the background... We have a mixer where mentees and potential mentors meet -after this event they then each will choose their top 3 choices for the mentor/mentee relationship. The problem typically arises where particular mentors become the "popular" choice among the mentees. This also happens the reverse with mentors choosing the popular mentees during the mixer.

Since we know both groups have 3 choices, can an auto matching sheet be created where on the first level - all mentors and mentees that have each other selected as 1st choice will be auto matched.

Then as a second step, the mentors 1st 2nd and 3rd choices would be considered and create a tiered structure to match mentees accordingly based on their 1,2,and 3rd choice as well. Priority should be given to Mentors as they are senior to mentees (ie a % compatability matrix - both members 1st choice match would be 100% compatible)

The list can also be sorted by seniority, pay, and other factors to work through the remaining candidates with similar 1,2nd, and 3rd choices...

And all the remaining non-matches can be randomly matched.

Any help cracking this one would be forever appreciated

Jerry