Optimal Mentor-Mentee Pairings Problem

You could try creating a matrix with your year 10 students along the horizontal axis and year 7 on the vertical axis.

Each combination would be given a score which would would be based upon the sum of the individual scores for your criteria with a weighting of your choice. e.g. use lookup functions to get the strengths and give a high score to where they are identical, a lower score to where they differ by one level etc.

A formula could then determine the maximum score in each row and column. The first set of pairings would be those where the maximum scores for row and column coincide.

The exercise could then be repeated with the parings assigned above removed from the analysis.

If you look up the values A,B,C for the student using the VLOOKUP function, you can further convert these to ASCII codes (using the ASC function) to give the numbers 65, 66 and 67 respectively. Then you can subtract these and use the absolute value (ABS function) to give you a measure of closeness.

Hello, just saw your post. Would you be willing to share your excel file that you landed on? Looking for similar.

This thread is nearly 4 years old! You can download the file in post#1.

Originally Posted by nagelgal

Well I just found the forum today. No need for the snarky reply. The original file doesn't show what formulas were used. Just trying to learn something new here on my own. Thanks for the unnecessary response.

The response was not unnecessary. If you had bothered to look, you would indeed see the file in post #1 is the only file on this thread. There were suggestions on how to improve that file, but no other file/s where uploaded

I think that nagelgal was asking ollyo2 to share the final workbook with his matching matrix, based on what was said in post #5. Unfortunately, olly02 has not been here since that day in 2018 when he last posted, so the workbook won't be forthcoming. Opening a new thread would be the best course of action here.