Rank Matching Problem

I have this situation at work but ut I’m not sure where I start on it….

Here is my situation:

I have 25 interviewers (int1, int2,…. int25) that will each interview 6 candidates out of a pool of 50 (cand1, cand2,…. Cand50). Each candidate will interview with 3 interviewers. Each interviewer will rank his candidates 1-6. Each candidate will rank their interviewer 1-3.

I would like to figure out how to set up a spreadsheet to best come up with a result to match up interviewers with candidates. Obviously, any time interviewer’s top choice equals a candidate’s top choice, this would be the best match. After that, I don’t know where to take this or how to set this up.

Anyone have any ideas?

Imagine you have the list of interviewers going across the sheet in row 1, from B1 to Z1, and the list of candidates going down the sheet in column A, from A2 to A51. You then have a 2-d grid (B2:Z51) where each candidate can be matched with each interviewer. You will need two such grids to record the ranks awarded - these could be in separate sheets, and one will record the ranking from the candidates to the interviewers, and the other will record the ranking given to each candidate from the interviewers.

It is then relatively easy in a 3rd grid to check for equal rankings in the same cell on the two rankings sheets. You can also count the number of rankings for each interviewer (in the interviewer sheet) and for each candidate (in the candidate sheet) to check that they have allocated 3 (or 6) ranks, and by summing the ranks for the candidates you will have some idea of the better (lower total) and worse (higher total) candidates.

Hope this helps.

Pete