Please help!!! Macro?

[nnat]

I run a peer mentor program at a university. I have 2 spreadsheets, one with mentor students – approx 300 rows (sheet1) and one with mentee students – approx 900 rows (sheet 2). I have the following criteria in A1-K1 in both sheets – first name, surname, sex, age, nationality, student ID, phone, email, campus, faculty and course. I match the students based on similarities in this order – faculty (J1) sex (C1) age (D1) nationality (E1).

What I am hoping to end up with is one spreadsheet with students from sheet 1 matched with students from sheet 2 based on the above similarities.

e.g. Jo, Blogs, male, 17-25, China, 11111, 555-4444, jo.blogs@uni.edu , city, Arts, Bachelor of Arts
matched with
Billy, Bob, male, 17-25, China, 22222, 555-3333, billy.bob@uni.edu, city, Arts, Bachelor of Arts

A mentor (sheet 1 students) can have multiple mentees (sheet 2 students) but a mentee can only have one mentor. Therefore, Students in sheet 1 can be matched with multiple students in sheet 2, however students in sheet 2 can only be matched with 1 student in sheet 1.

Any help available on this would be greatly appreciated!

(I am using windows 7 and Excel 2007)
<!-- / message -->

 

[Hermanito]

This is not a trivial thing to do...

Suppose you have 3 very similar mentors and 10 matching mentees... Which mentee do you link to which mentor? Does one mentor get 6 mentees, while the other two mentors get only 2?

How do you decide how many mentees get linked to a particular mentor?

That is just one issue, I can think of many more for which you need to establish rules or guidelines... (even saying: match as close as possible and distribute as evenly as possible is a guideline; but now it's unclear what you want, at least to me )