Match Mentors to Mentees using the survey data responses

[Shrooti]

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:

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

Appreciate your help.

 

[vletm]

Shrooti
Hint: You should upload a sample Excel-file here.
Who else would like to guess, where do Your given link goes? ... google sheets?

 

[Shrooti]

Shrooti
Hint: You should upload a sample Excel-file here.
Who else would like to guess, where do Your given link goes? ... google sheets?

Since this is my first post, it doesn't allow me to attach any files, unfortunately. I, hence, shared the Google Sheets file.

 

[Shrooti]

The top priority matching criteria is:
- Gender preference
- Business Unit Preference
- Region
- Any one skill match (lived experience, professional skills, leadership skills)

Here's the Mentor Data:

EID	Mentor	Email ID	Gender	Business Unit	Region	Time Zone	Business Unit Preference	How many mentees you want to mentor?	Lived Experience	What are the Professional Skills you have?	What are the Leadership Skills you have?
E01	Joe Smith	joesmith@email.com	Male	Data and Services	AP	IST (India Standard Time)	No	2	Work/life integration	Business Acumen	Building self-awareness
E02	Mary Brown	marybrown@gmail.com	Female	Cyber and Intelligence Solutions	NAM	EST (Eastern Standard Time)	No	2	LGBTQ	Consumer Experience	Building self-awareness
E03	Rohit Arwala	rohit@email.com	Male	Operations and Technology	EUR	GMT (Greenwich Mean Time)	Yes	1	Parenting or caregiving	Business Acumen	Building self-awareness

Mentee Data:

EID	Full Name	Email ID	Business Unit	Region	Time Zone	Business Unit Preference	Gender Preference	Lived Experience	What are the Professional Skills you want to be mentored on?	What are the Leadership Skills you want to be mentored on?
E04	Jenna Brown	jb@gmail.com	Strategic Growth	AP	CST (Central Standard Time)	Yes	No preference	Work/life integration	Business Acumen	Building self-awareness
E05	Courtney Athina	ca@email.com	Law, Franchise, and Integrity	NAM	GMT (Greenwich Mean Time)	Yes	Female	Parenting or caregiving	Influence & Engagement	Building self-awareness
E06	Anna Cho	ac@email.com	Operations and Technology	EUR	IST (India Standard Time)	Yes	No preference	Intersectionality	Business Acumen	Collaboration

Appreciate your help.

 

[vletm]

Shrooti
If You would like to get replies, please try again to upload an Excel-file.
You're the 1st new member, who has given any note that cannot upload an Excel-file here.
Did You press [ Attach files ]-button and tried to select that file.
... oh ... that table ... won't help much.
... and one more hint:
You should able to show expected results there too - even manually solved.

 

[Shrooti]

Attaching the file. Will appreciate if anyone can help.

 

[vletm]

Shrooti
Where is You should able to show expected results there too - even manually solved. ?
How would someone use that?

 

[AlanSidman]

crossposted: https://www.mrexcel.com/board/threa...tees-using-the-survey-data-responses.1191621/

Please read: https://www.excelguru.ca/content.php?184

 

[Shrooti]

Shrooti
Where is You should able to show expected results there too - even manually solved. ?
How would someone use that?

If you don't have any solution, please stop commenting with irrelevant and negative comments.

 

[vletm]

Shrooti
I've had some ideas for solution,
but I would like to know Your idea as I've tried to get answers.
--- expected results? as well as how to use?
Many details matters, what kind of solution? eg size of used data.

As moderator, I also have to note about Cross-Posting, which has clearly written in Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Ps. Same kind of rules about Cross-Posting are in every Forum.

 

[vletm]

Shrooti
In this time - I tested something
... You've missed few needed details.
I offer one non-formula sample solution based Your given data.
I could show 'matches' based (Q4) Gender and Skills, if any of those match (Q12 & Q13).
Other two Q5 and Q7 could 'switch ON' by selecting cell E1 and G1 from Mentors-sheet ( those will get yellow).
( I didn't find valid combinations. )
After You select Matches-sheet ( results ),
You'll see which Mentors and Mentees match.
You could add/have as many Mentors as well as Mentees - as You've done with Your sample file.
Note: I've tested with some possible cases with this sample.

 

[Shrooti]

Shrooti
I've had some ideas for solution,
but I would like to know Your idea as I've tried to get answers.
--- expected results? as well as how to use?
Many details matters, what kind of solution? eg size of used data.

As moderator, I also have to note about Cross-Posting, which has clearly written in Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Ps. Same kind of rules about Cross-Posting are in every Forum.

Hi,

About cross-posting, I wasn't aware of the rule and, hence, didn't add the link.
I'm not able to edit the original post either, so cross-posting the link here. https://www.mrexcel.com/board/threa...tees-using-the-survey-data-responses.1191621/

As far as more details are concerned, listing them below:
- We have data of mentors & mentees ranging from 500-600 entries each, approximately. Matching the data manually is not feasible.
- The most important criteria is considering the preferences of the mentees are comfortable with
1) to prefer a female/male mentor
2) to prefer mentor from same business unit
3) to prefer mentor who has at least one of the 'professional' or 'leadership' skills matched
4) to prefer mentor from the same region

Either all 4 above criteria or at least 1, 2, & 3 should match from the above list.

The final file can have one sheet with mentors and it's details, second sheet with mentees and details.
The matched results can displayed either in a separate tab with the list of mentors with the names of mentees mentioned in the adjacent column (like mentioned in your solution file) OR in the 'mentor details' sheet whichever, method is feasible. the main objective will be to have the mentors and mentees matched.

Glad to help you with any further questions.

Appreciate all your help.

 

[vletm]

Shrooti
The Front Page has link New Users - Please Start Here.
Of course, that link could check and refresh anytime - especially,
before the first posting.

As I wrote,
You could add/have as many Mentors as well as Mentees - as You've done with Your sample file.
About Your
1).. 4) ... those should take care.
Either all 4 above criteria or at least 1, 2, & 3 should match from the above list.
I could show 'matches' based (Q4) Gender and Skills, if any of those match (Q12 & Q13).
Other two Q5 and Q7 could 'switch ON' by selecting cell E1 and G1 from Mentors-sheet ( those will get yellow).
( I didn't find valid combinations. )
There should be data to test and verify that all criteria can find - without that, there could be more challenges.
Now, Q4 and Q12&Q13 have to [un]select manually.
... So, far this option is only detail, which I could modify.

The final file can have one sheet with mentors and it's details, second sheet with mentees and details.
Aren't those there = Your two sheets?

The matched results can displayed either in a separate tab with the list of mentors with the names of mentees mentioned in the adjacent column (like mentioned in your solution file) OR in the 'mentor details' sheet whichever, method is feasible. the main objective will be to have the mentors and mentees matched.
What is missing from Matches-sheet?
... I noticed later that some procedures could modify - not modified.

Need answers to questions, that I could do something.
There can be combinations (Mentors - Mentees) which do not have match, as You have noted.
... what to do with those?

I need Your answers to my questions,
that I could modify something more.

 

[Shrooti]

Shrooti
The Front Page has link New Users - Please Start Here.
Of course, that link could check and refresh anytime - especially,
before the first posting.

As I wrote,
You could add/have as many Mentors as well as Mentees - as You've done with Your sample file.
About Your
1).. 4) ... those should take care.
Either all 4 above criteria or at least 1, 2, & 3 should match from the above list.
I could show 'matches' based (Q4) Gender and Skills, if any of those match (Q12 & Q13).
Other two Q5 and Q7 could 'switch ON' by selecting cell E1 and G1 from Mentors-sheet ( those will get yellow).
( I didn't find valid combinations. )
There should be data to test and verify that all criteria can find - without that, there could be more challenges.
Now, Q4 and Q12&Q13 have to [un]select manually.
... So, far this option is only detail, which I could modify.

The final file can have one sheet with mentors and it's details, second sheet with mentees and details.
Aren't those there = Your two sheets?

The matched results can displayed either in a separate tab with the list of mentors with the names of mentees mentioned in the adjacent column (like mentioned in your solution file) OR in the 'mentor details' sheet whichever, method is feasible. the main objective will be to have the mentors and mentees matched.
What is missing from Matches-sheet?
... I noticed later that some procedures could modify - not modified.

Need answers to questions, that I could do something.
There can be combinations (Mentors - Mentees) which do not have match, as You have noted.
... what to do with those?

I need Your answers to my questions,
that I could modify something more.

Thank you so much for providing a consolidated file meeting all the requirements.

To answer your below question; I'm getting more clarity on this part and will get back to you as soon as I can:
"Need answers to questions, that I could do something.
There can be combinations (Mentors - Mentees) which do not have match, as You have noted.
... what to do with those?"

On your below point, I'm not able to understand your question:
"What is missing from Matches-sheet?
... I noticed later that some procedures could modify - not modified."

Appreciate all your help.

 

[Shrooti]

Thank you so much for providing a consolidated file meeting all the requirements.

To answer your below question; I'm getting more clarity on this part and will get back to you as soon as I can:
"Need answers to questions, that I could do something.
There can be combinations (Mentors - Mentees) which do not have match, as You have noted.
... what to do with those?"

On your below point, I'm not able to understand your question:
"What is missing from Matches-sheet?
... I noticed later that some procedures could modify - not modified."

Appreciate all your help.

Hi,

Thank you for sharing the file. It was really helpful. However, since the data is very large, the script is taking a very long time to process. I've been running script since Tuesday and is still not complete. I would really appreciate if you could suggest an alternative to proceed with this matching.

appreciate your help.

Best regards,

Yogita

 

[vletm]

Shrooti
Something could do, but without large test data - it'll be a challenge to verify.

 

[Shrooti]

Shrooti
Something could do, but without large test data - it'll be a challenge to verify.

Okay. Then what would be the best way forward.

 

[vletm]

Shrooti
As I tried to explain ... but without large test data ...
I need to have a lot of (test) data to find out - which takes time? >> how to make it faster?
You should able to send me an Excel-file.