• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dating sheet! Who is the most suitable match?

Status
Not open for further replies.

Dvd42

New Member
Now I have your attention :) what I'm actually looking for runs on a very similar premise, but for mentors and mentees? I have a two lists of people and attributes including location, department and institution they are member of, and I'd like to be able to assign the most suitable people to each other. So for example a doctor who works in London will be paired with a junior doctor also in London from the same institution.

I've been looking at various concatenations and then vlookups to get matches, but this gives the exact match. What I'd like is for it to still work when say only 2 out of three criteria are met as well, but for the best matched pairs to be matched first?

Thank you for your help!
 
It'd really help if you'd upload an dummy file of approximately what you're working with. Also, is there a priority for the matching criteria? You mentioned location first, but could you use a 2,3,4,....n rating for the other attributes?
 
Hi Mike, thanks for taking a look!

The only mandatory criteria is that they must be from the same institution. Same location is preferable, and then same department is preferable as well but less so.

I've put them in order of importance on the sheet. Once I have the way of allocating according to these criteria my next step will be to work out a way to track who has had people allocated to them. That next step I hope should be a bit easier.

ps all the names used are random names so no worries for privacy.
 

Attachments

  • MENTOR_MENTEE.xlsx
    71.7 KB · Views: 32
Just looking at your spreadsheet. There will obviously be situations where there are too many or too few from the two groups to match up everyone. What were you thinking about as this starts happening through the Institutions and locations? Match the first x and leave the rest or start assigning additional mentees to mentors until all are assigned?
 
Thanks Mike,

The aim is once the actual names are in that there will be always more mentors spaces (as can get three students per mentor). The location is the only mandatory requirement, you don't have to be a mentor from the same institution but it is desirable, and then department is just a nice to have.

I can interrogate the data back to highlight out if there's too many pairings where institution doesn't match up or flags when there are not enough mentors, I just can't get my head around matching people up by weighted criteria, but sometimes not those exact criteria will be matched (so best fit as I said before)
 
@Dvd42 Take a look at the attached file. I was playing with different weightings for the three factors and I'm pretty close. I can now differentiate between Institutions, locations, and even departments by varying the acceptable range on the Matrix tab. I don't like the big matrix, but it's what I came up with since you need to evaluate each student against each instructor. It's probably possible to do this with a defined 2D matrix, but I didn't go there.

*Anyone else looking at this thread, feel free to chime in with a better way.
 

Attachments

  • MENTOR_MENTEE.xlsx
    598.9 KB · Views: 44
I really like the matrix method!
I've taken a more brute force approach which has some advantages but also has limitations. I'm using Excel solver to do the best match between mentor and mentee with a similar sort of rating system. This works pretty well, but can only allocate up to 200 variables, and no more unless I install an add in which my computer will not be allowed to run.
 

Attachments

  • MENTOR_MENTEE.xlsx
    108.7 KB · Views: 18
  • MENTOR_MENTEE_HELPINGHAND.xlsx
    575.5 KB · Views: 18
@Dvd42 OK, another concept. I got tired of doing the weightings as the sensitivity shifted too far with the number of locations. This is more brute force, but the end result is a sorted listing of best matches per graduate.

The tab Matrix-2 encodes the level of match (4 all factors: 1 Institution only) with the Mentor number divided by 1000. The Condensed tab sorts the matching Mentors for each Graduate by level of match and Mentor number. As long as there is at least an institutional match, every Graduate will have somebody to go to.

It sort of needs some way of sorting out over used Mentors, but it seems to be getting really close now.
 

Attachments

  • MENTOR_MENTEE_HELPINGHAND.xlsx
    720.9 KB · Views: 21
We are getting close yep!

So am I right in saying that the numbers reference mentors/mentees? I've added a vlookup thing to show the matches more clearly now. SO my next question is. If we have got to the ranked list for each mentee showing which mentors match to them (your work attached), and we have a way of allocating the mentors so the maximum amount of people are happy with their matching (my work attached). Is there anything else that can be done to optimise the workflow?

I'm really happy though with what we have so far though its a great help!
 

Attachments

  • MENTOR_MENTEE_HELPINGHAND.xlsx
    929.8 KB · Views: 18
  • MENTOR_MENTEE.xlsx
    108.7 KB · Views: 10
@Dvd42 I've done a few things on this. There's a new Resolved Mentor tab that simply pulls the mentor numbers out from the Condensed tab. This is needed for the loading calculation. I also simplified how you were resolving the Mentor names in the Name tab. To try and reduce the file size, I limited the Condensed, Resolved Mentor, and Names tab functions to rows 1:25. This is should be more than adequate since you're only interested in the top few lines. I also added some columns to the tables in the Mentees and Mentors tabs.

The Mentee tab is where the selection happens. Since there are several factors in play, I wasn't able to come up with a function to make the final Mentor selection automatic. I used conditional formatting in the Best Mentor Match column to identify Mentors with more than 5 graduates. You can then decide if the next best Mentor should take that graduate by putting any character in the Replace column. A similar conditional format is in the Mentor column for a final load check on the Mentors. You could add another column for the third best match and continue the concept, if needed.

The Mentor tab has a Best Match Count and Final Match Count so you can watch how the general loading is going.

There are a couple of named areas such as MentorFactors, MentorNames and GradFactors that are associated with the Mentor and Graduate tables. They're critical to the sheet function, so be careful with further modifications.
 

Attachments

  • MENTOR_MENTEE_HELPINGHAND (1).xlsx
    635.3 KB · Views: 97
Yes I saw the named ranges took a bit to get my head around using them. I'll try the tool with some actual data and hopefully everything will go well :)
 
@Dvd42 It was a fun problem to work on. Let me know if you have any other questions or if something doesn't work right.
 
Looking at this many years later. I'm totally new to using matrices in excel.
One thing I do not understand, and thus can not replicate, is how IF function used in the main matrix sheet is linking back to the graduate and mentor tabs?
Any able to explain this to me?
77585
 
Status
Not open for further replies.
Back
Top