Marc L
Excel Ninja
Same result with the Mentees worksheet sorted in descending order
but it may depend on the list order according to Mentors' free Mentees # …
Match Mentors to Mentees (With large test data)
- Thread starter cheq
- Start date
Yes, I got what you mean with the factor of Mentors' free Mentees #. I will further test your #27 code with my real data. Thanks for your support!!
Sorry for the inconvenience caused.
In short, my latest ideas are matching according to the priority. If no more mentor-mentee can satisfy condition 1), then doing the 2) and so on.
Priority for the matching: (As many as matching with 1) & 2) & 3) below is preferred)
1) Programme & Code matched
2) Programme matched
3) Code matched
4) Gender matched
5) Remaining Mentees and Mentors just casually matched
*Additionally, I would like to have the flexibility that all-female mentors can match with male/female mentees, while male mentors can only match with male mentees.
My brief idea is to add 1 option of "No preference" just for the Gender of Mentors Sheet, then I can replace all female mentors with "No preference". Thus, the gender data of the Mentors sheet will be "Male" &"Female" & "No preference". Please suggest if there is a simpler method.
Thanks a lot!
Last edited:
Regarding the code in #27, could you please help to add the "Code" for 'Matching with the following sequence. I hope it will not cause you too much time! Thank you in advance!!
1) Programme & Gender & Code
2) Programme & Gender
3) Programme
4) "Code"
5) Gender
Marc L
Excel Ninja
I try two different ways but nothing matches for Code alone after the previous steps (like for Gender & Code),
maybe with a better attachment …
Another question according to
So for this point #5, my demonstration should only allocate the female mentors ?
I revised the fake data set for your investigation. Thanks a lot!
I totally understood why you asked me the "ideal logic worksheet layout" in the beginning.
Steps summary :
• 1 : Programme & Gender & Code
• 2 : Programme & Gender
• 3 : Programme
• 4 : Gender & Code
• 5 : Code
• 6 : Gender
• 7 : remaining female mentors allocated to remaining mentees.
8 : remaining male mentors allocated to female mentees? (For example, 3 males mentors and 20 female mentees left)
I learned a lot throughout this learning journey. Highly appreciated!
Marc L
Excel Ninja
According to points 7 & 8 then it shoud be an unique point like remaining mentors allocated to remaining mentees ?
Whatever female or male mentors remaining as according to point 6 one mentors gender can be entirely allocated
so it may remain only a single mentors gender, female or male !
So my point just follows your post #56 which is different than your last post !
So after the point 6, if a mentors gender is remaining, whatever female or male,
this gender must be allocated to the opposite gender mentees remaining
or according to your post #56 only female mentors remaining must be allocated to the male mentees remaining ?
May I clarify that for point 6: Gender, you mean
A: female mentors-> female mentees; male mentors-> male mentees or
B: female mentors-> male/female mentees; male mentors-> male mentees?
B is what I need, it will absolutely remain only a single mentors gender. Then, the remaining mentor should be allocated to the opposite gender mentees. I hope I do not confuse you more.
• 6 : Gender
• 7 : remaining female mentors allocated to remaining mentees.
8 : remaining male mentors allocated to female mentees? (For example, 3 males mentors and 20 female mentees left)
Just want to make sure we are on the same page. As point 6 is only for matching the same genders of mentor-mentee, thus it may happen that only male mentors and females mentees left (say, 10 male mentors and 80 female mentees left). The remaining mentors will not process in point 7 and thus point 8 is needed. I hope I have not misled the logic.
cheq
# StillI missing: I hoped to get an answer about Your three ... four case.
# Do data need to be some specific order that You could get expected results? ... because You've written next sentence:
I figured out the problem. It's caused because column A (Mentee ID) in the mentee list sheet is not in ascending order.
# I tried to find logic from Your previous writings
Latest idea: ... For the specific order, I hope to have: ...
# I tested to add one more criteria. ... there do not need anything new function ...
# Programme & Gender would give different results than Gender & Programme?
From my understanding, it would not give a different value.
... but with my sample, it will/would to give different results.
# for the matching: (As many as matching with 1) & 2) & 3) below is preferred)
... and after that Gender should overrule 1,2,3.
If Gender should take care - as the highest - then there should be enough Female/Male Mentors?
Same way there could be possible: Male/Male, Female/Female, Male/Female & Female/Male -combinations even with "No preference"-gender.
...
Seems there has changed details this-way-that-way - windy.
# StillI missing: I hoped to get an answer about Your three ... four case.
# Do data need to be some specific order that You could get expected results? ... because You've written next sentence:
I figured out the problem. It's caused because column A (Mentee ID) in the mentee list sheet is not in ascending order.
# I tried to find logic from Your previous writings
Latest idea: ... For the specific order, I hope to have: ...
# I tested to add one more criteria. ... there do not need anything new function ...
# Programme & Gender would give different results than Gender & Programme?
From my understanding, it would not give a different value.
... but with my sample, it will/would to give different results.
# for the matching: (As many as matching with 1) & 2) & 3) below is preferred)
... and after that Gender should overrule 1,2,3.
If Gender should take care - as the highest - then there should be enough Female/Male Mentors?
Same way there could be possible: Male/Male, Female/Female, Male/Female & Female/Male -combinations even with "No preference"-gender.
...
Seems there has changed details this-way-that-way - windy.
# StillI missing: I hoped to get an answer about Your three ... four case.
Sorry that I missed this question. what do you mean by three... four case?
# Do data need to be some specific order that You could get expected results? ... because You've written next sentence:
I figured out the problem. It's caused because column A (Mentee ID) in the mentee list sheet is not in ascending order.
I did not know that the sequence (ascending order) has impacts initially. I will not arrange the data and would just leave the ID in ascending order.
# I tried to find logic from Your previous writings
Latest idea: ... For the specific order, I hope to have: ...
Yes. I thought I knew what I want before but with the efforts from you and Marc L, I know that I should think more broadly.
# I tested to add one more criteria. ... there do not need anything new function ...
# Programme & Gender would give different results than Gender & Programme?
From my understanding, it would not give a different value.
I tried the data again manually with a filter, i understood there was difference between Programme & Gender and Gender & Programme. Initially, I mixed it up with the concept of "Match".
... but with my sample, it will/would to give different results.
# for the matching: (As many as matching with 1) & 2) & 3) below is preferred)
... and after that Gender should overrule 1,2,3.
If Gender should take care - as the highest - then there should be enough Female/Male Mentors?
Same way there could be possible: Male/Male, Female/Female, Male/Female & Female/Male -combinations even with "No preference"-gender.
...
Seems there has changed details this-way-that-way - windy.
I am sorry to admit that I changed many times! Please forgive me! Regarding the possibility of Male/Male, Female/Female, Male/Female & Female/Male -combinations even with "No preference"-gender, honestly speaking, I don't know how to consider them. From my standpoint, I just want to have as many matching pairs established with the following priority. That means the logic should fulfill condition 1 first, then 2, 3, and so on. (For instance, 5 pairs in condition 1 & 10 pairs with condition 2 are better than 4 pairs with condition 1 & 100 pairs with condition 2 ) My real sample has around 75 female mentors & 25 male mentors.
This is the best logic I can think of at the present moment:
• Condition 1 : Programme & Gender & Code
• Condition 2 : Programme & Gender
• 3 : Programme
• 4 : Gender & Code
• 5 : Code
• 6 : Gender
• 7 : remaining female mentors allocated to remaining mentees.
• 8 : remaining male mentors allocated to female mentees
# StillI missing: I hoped to get an answer about Your three ... four case. >> #42 Reply
# Do data need to be some specific order that You could get expected results?
So, Your answer seems to be Yes, it needs.
ps. There are many other details, which makes differences in results ... if eg try to compare mine or Marc L's.
# Genders - Female / Male / Non-binary
Someone would accept only same or only other gender and someone would say - 'no matter'.
Now, You logic has Male/Male and Female/Female, ( if it matches then that would be plus one point (or number of criteria points) )
... but if it do not match, is something absolute no? ... in my used logic - it's not.
# My real sample has around 75 female mentors & 25 male mentors.
How many mentees are those ready to take?
... There are almost same number of those or
female mentors would have 10 and male mentors would have 30?
# This is the best logic I can think of at the present moment:
... think ...
I've use other logic - as I've have few times written - that's one reason with differences with results.
I can continue with my logic and try to use some of Your thoughts.
# Do data need to be some specific order that You could get expected results?
So, Your answer seems to be Yes, it needs.
ps. There are many other details, which makes differences in results ... if eg try to compare mine or Marc L's.
# Genders - Female / Male / Non-binary
Someone would accept only same or only other gender and someone would say - 'no matter'.
Now, You logic has Male/Male and Female/Female, ( if it matches then that would be plus one point (or number of criteria points) )
... but if it do not match, is something absolute no? ... in my used logic - it's not.
# My real sample has around 75 female mentors & 25 male mentors.
How many mentees are those ready to take?
... There are almost same number of those or
female mentors would have 10 and male mentors would have 30?
# This is the best logic I can think of at the present moment:
... think ...
I've use other logic - as I've have few times written - that's one reason with differences with results.
I can continue with my logic and try to use some of Your thoughts.
Marc L
Excel Ninja
Ok so my new point 7 is the remaining mentors must be allocated to remaining mentees so post #56 to be forgotten …
Another point : all the mentors' mentees # are pretty the same like for example 10 or it could be have some gap
like one has 5 and another as 40 for example ? Relative to speed execution …
Still cell G3 as default mentees # or another cell or a hardcode value within the VBA procedure ?
cheq
Mentees per Mentor could be default eg two ... user will give a message, if it should be more.
Priority of criteria should mark in top row as above ( without those - no work ).
There could be 'more data' as sample ABC - without priority - no effect.
All selections effects results!
The logic of sample is still same.
Mentees-Mentors -sheet has 'quick filtering'-option.
Mentees per Mentor could be default eg two ... user will give a message, if it should be more.
Priority of criteria should mark in top row as above ( without those - no work ).
There could be 'more data' as sample ABC - without priority - no effect.
All selections effects results!
The logic of sample is still same.
Mentees-Mentors -sheet has 'quick filtering'-option.
May I clarify that "The logic of sample is still same.", that mean the logic mentioned in which post?
When I have a "10" no. of "Mentees per Mentors", I tried to click "Cancel" to "Mentees per Mentors: Need to be atleast 15" and it seems to work too. Are there any problems with less than "15" no. "Mentees per Mentors" then?
Mentees-Mentors -sheet has 'quick filtering'-option. -> This function is useful. Thanks for the considerate amendment!
You’re a life saver! You made my day! It is exactly what I want with a very clear & understandable result sheet.
Another follow-up question:
After the first matching (around 100 mentors matching with 1000 mentees), we may have some additional mentees from time to time. How could I use the code to match mentor-mentee again?
I attempted by putting 48 mentees with 98 mentors (1 mentee per mentor), but the result looked weird. (For example, the school code is not matched, but it still shows a black text color "code". Attached is my excel file for your information.
cheq #71
# the logic mentioned in which post?
> #36 reply and after that those settings which You do in the beginning
# Are there any problems with less than "15" no. "Mentees per Mentors" then?
There are none problems, if You keep Your 10 - it's users choice.
... the results could get quicker, but about 500 Mentees won't get Mentors. It could be a challenge for those Mentees.
My sample should take care that case. (#73)
# Do You still hope to get that Gender-case to be more useful?
# the logic mentioned in which post?
> #36 reply and after that those settings which You do in the beginning
# Are there any problems with less than "15" no. "Mentees per Mentors" then?
There are none problems, if You keep Your 10 - it's users choice.
... the results could get quicker, but about 500 Mentees won't get Mentors. It could be a challenge for those Mentees.
My sample should take care that case. (#73)
# Do You still hope to get that Gender-case to be more useful?
I can’t thank you enough. I really really appreciate your continuous assistance.
# Do You still hope to get that Gender-case to be more useful?
I am applying more source data to see the matching result. Please hold for a few days, and I will update here asap.