Hi,
I have basic knowledge of MS Excel Formulae and have been trying to automate a survey results file to match Mentors to the best suitable Mentees based on the below criteria:
- gender preference. this survey is for only women mentees, so they can choose a female or a male mentor
- business unit preference
- number of mentees a mentor can take up
- the professional skills mentee is keen on getting mentorship for
- the leadership skills a mentee wants mentorship for
Below are the dummy details of the survey data collected for mentors and mentees respectively:
MENTORS ---
MENTEES --
This is just a dummy data. However, the actual data is going to exceed approximately 500 entries or more, each for the mentors and for mentees. I'm looking for a formulae where by just pasting the data for mentors and mentees, i would have the best matched mentees across each mentor. Would appreciate your help in providing a formula.
I have basic knowledge of MS Excel Formulae and have been trying to automate a survey results file to match Mentors to the best suitable Mentees based on the below criteria:
- gender preference. this survey is for only women mentees, so they can choose a female or a male mentor
- business unit preference
- number of mentees a mentor can take up
- the professional skills mentee is keen on getting mentorship for
- the leadership skills a mentee wants mentorship for
Below are the dummy details of the survey data collected for mentors and mentees respectively:
MENTORS ---
Full Name | Email ID | Gender | Business Unit | Area of Expertise | Region | Time Zone | How many mentees you want to mentor? | What are the Professional Skills you have? |
| |
Joe Smith | abc@gmail.com | Male | Customer Service | Sales | North America |
| 2 | Consumer Experience | Business self-awareness | |
Mary Brown | mary.b@rediff.com | Female | Marketing Analytics | Data Analysis | Europe |
| 1 | Influence and Engagement | Collaboration | |
Rohit Arwala | rarwala@yahoo.com | Male | Financial Services | Accounting | Asia Pacific |
| 2 | Creativity/Cognitive Flexibility | Adaptability and Agility |
MENTEES --
Full Name | Email ID | Gender Preference | Business Unit | Area of Expertise | Region | Time Zone | What are the Professional Skills you want to be mentored on? |
| |
Bree Lars | xyz@gmail.com | No preference | Customer Service | Sales | North America |
| Consumer Experience | Business self-awareness | |
Marcelle White | pqr@rediff.com | Yes, only female | Marketing Analytics | Data Analysis | Europe |
| Influence and Engagement | Collaboration | |
Priti Khandelwal | abz@yahoo.com | Yes, only female | Financial Services | Accounting | Asia Pacific |
| Creativity/Cognitive Flexibility | Adaptability and Agility |
This is just a dummy data. However, the actual data is going to exceed approximately 500 entries or more, each for the mentors and for mentees. I'm looking for a formulae where by just pasting the data for mentors and mentees, i would have the best matched mentees across each mentor. Would appreciate your help in providing a formula.