• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match Mentors to Mentees using the survey data responses

Shrooti

New Member
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:

Appreciate your help.
 
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
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.
 
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:

EIDMentorEmail IDGenderBusiness UnitRegionTime ZoneBusiness Unit PreferenceHow many mentees you want to mentor?Lived ExperienceWhat are the Professional Skills you have?What are the Leadership Skills you have?
E01Joe Smithjoesmith@email.comMaleData and ServicesAPIST (India Standard Time)No2Work/life integrationBusiness AcumenBuilding self-awareness
E02Mary Brownmarybrown@gmail.comFemaleCyber and Intelligence SolutionsNAMEST (Eastern Standard Time)No2LGBTQConsumer ExperienceBuilding self-awareness
E03Rohit Arwalarohit@email.comMaleOperations and TechnologyEURGMT (Greenwich Mean Time)Yes1Parenting or caregivingBusiness AcumenBuilding self-awareness

Mentee Data:


EIDFull NameEmail IDBusiness UnitRegionTime ZoneBusiness Unit PreferenceGender PreferenceLived ExperienceWhat are the Professional Skills you want to be mentored on?What are the Leadership Skills you want to be mentored on?
E04Jenna Brownjb@gmail.comStrategic GrowthAPCST (Central Standard Time)YesNo preferenceWork/life integrationBusiness AcumenBuilding self-awareness
E05Courtney Athinaca@email.comLaw, Franchise, and IntegrityNAMGMT (Greenwich Mean Time)YesFemaleParenting or caregivingInfluence & EngagementBuilding self-awareness
E06Anna Choac@email.comOperations and TechnologyEURIST (India Standard Time)YesNo preferenceIntersectionalityBusiness AcumenCollaboration

Appreciate your help.
 
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.
 
Attaching the file. Will appreciate if anyone can help.
 

Attachments

  • Mentor-Mentee Matching Survey Results.xlsx
    48.2 KB · Views: 9
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:
  • 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.
 
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.
 

Attachments

  • Mentor-Mentee Matching Survey Results.xlsb
    79.5 KB · Views: 14
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:
  • 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.
 
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.
 

Attachments

  • Shrooti.xlsb
    95.1 KB · Views: 32
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.
 
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
 
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.
 
Back
Top