Match Mentors to Mentees (With large test data)

[cheq]

Hi all,

I have been surfing the internet to find relevant VBA modules to fulfill my needs. Unfortunately, I was unable to find a solution.

Normally, I would have 100 mentors and 1300 mentees (roughly, 1 mentor to 13 mentees). I hope I can generate a list of matching results. (See the spreadsheet "(Ideal result)Mentees - Mentors")

Prefetching criteria are:
- Programme (Q5)
- Gender (Q4)
- Code (Q6)

Ideally, those mentor-mentees who fulfilled all the above 3 criteria can be matched first. Next, those with 2 out of 3 criteria would be matched, followed by 1 out of 3 criteria matched. Lastly, the remaining mentors and mentees can be matched (Just simply putting on sequence/randomly is fine)

At last, it would be nice to have the option to choose the no. of mentees required by the mentor (Q7). It's fine if this function does not work, then just each mentor has an average no. of mentees.

Appreciate your help. Thank You very much!

 

[Marc L]

Hi, Excel version, OS ?​

​

Elaborate at least your ideal result worksheet as guessing can't be coding …​

​

 

[cheq]

Sorry for not explaining the result worksheet clearly. I am using Excel 2016, windows OS. (I can access to Microsoft 365 as well)

Regarding ideal result worksheet, the basic is matching mentor-mentees as said above. If possible, the sheet can show which criteria have been met as well, say programme/gender/code matched.

Practically, I just need to have each mentor pairing with 13-15 mentees without overlapping mentees. If fulfilling all three criteria seems impossible, I hope to have at one criteria fulfilled, which is programme.

Thank you for your reply and please let me know if you need further information.

 

[cheq]

I tried to apply the VBA project retrieved from the post below.

Match Mentors to Mentees using the survey data responses

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...

chandoo.org

It seems that my proposed result worksheet is possible, but given the large no. of sample data, the result needs to process for a very long time.

 

[vletm]

cheq
That sample has few modes - there is possible to run it slower and faster.
Isn't that true, if more data then it will take more time. What is a very long time?
That sample has many more criteria than Your and that will take longer time too.
One more thing ... that's still a sample - not ready at all.

 

[Marc L]

cheq : so the ideal result of your attachment does not well reflect your expected list ?!​

​

 

[cheq]

My ideal result is just like the sheet i provided in post 1. I just afraid that it is not possible to create.

 

[Marc L]

Which sheet ? If ideal result not possible until you explain at least the logic to apply to build exactly this layout​

as it does not exactly match the source sheets !​

​

 

[cheq]

cheq
That sample has few modes - there is possible to run it slower and faster.
Isn't that true, if more data then it will take more time. What is a very long time?
That sample has many more criteria than Your and that will take longer time too.
One more thing ... that's still a sample - not ready at all.

My VBA knowledge is too weak to understand and tailor-make the criteria to meet my criteria. I took 2+ hours to run the data and only around 250 mentees were loaded (by checking the names in the rows), and the excel application lagged and processed even slower gradually. (Just like what you described) It is a pity that I cannot complete a full trial to see the result.

Thank you so much for creating the orginal excel file for the inspiration of making the mentoring matching possible.

 

[Marc L]

As under any database software that should need few seconds, far less than a minute.​

​

 

[cheq]

Which sheet ? If ideal result not possible until you explain at least the logic to apply to build exactly this layout as it does not exactly match the source sheets !​

Please refer to the spreadsheet of "(Ideal result)Mentees - Mentors" for my ideal result. For more details of the logic, maybe the VBA project appended in the excel can explain.

Source sheets can be referred to Mentors sheet and Mentees sheet. By matching the criteria (Q4 - Q6) of both Mentors and Mentees sheets, I hope to generate the ideal result list ( "(Ideal result)Mentees - Mentors" ).

Thanks a lot!

 

[cheq]

As under any database software that should need few seconds, far less than a minute.​

I just self-learn excel VBA and do know database software. Could you explain a little bit or give me some keywords to search for further info. Many thanks!!

 

[Marc L]

As a database software can be 50 times - even 100 ! - faster ...​

​

You misread my posts !​

As nothing explain your ideal logic worksheet layout,​

as guessing can't be coding so not possible to reproduce exactly this layout​

without its relative expected & required elaboration ...​

​

 

[cheq]

I believe I still do not understand the meaning of ideal logic worksheet layout. Thats why I can only be able to provide the source data i own, and ask for your help to propose any ideas or solution to attain what I need.

I am looking for info about microsoft access. But I still get no ideas how to apply Microsoft Access to my needs. "Join SQL" seems to be related, but I am still learning more about it.

 

[Marc L]

So create yourself a worksheet layout according to what you understand,​

according to the logic to apply for each column you are able to well describe / elaborate / explain if necessary …​

​

 

[cheq]

I created a "Processing Sheet" in the attached file. *For demonstration, I assume each mentor pairs with 2 mentees only for an easier explanation.

For the logic:
1) Put all the mentor-mentee combinations into the processing sheet
2) Use the filter function to count the criteria that were matched in the box (F6:M10)
3) If 3-score was shown (three criteria were matched), directly put the mentee names next to the mentors (D6:E6)
4) Then those matched mentors and mentees can be excluded from the filtering processing
5) If 0-2 scores were shown, the excel continues to count until all the remaining scores have been counted (F6:M10)
6) After the table shows the remaining scores (0/1/2 in the remaining cells), for those with a 2-score, put the mentee names next to the mentors, followed by 1-score
7) Finally only 0 scores will remain, then just put the mentee names next to the mentors with any sequence
8) Therefore, all mentors and mentees can be paired

I hope the above logic helps. I'm basically following the concept created by [vletm], highly appreciated it!!

 

[Marc L]

According to the previous post attachment an Excel basics VBA demonstration to paste only to the result worksheet module​

which lists each mentee best matches if any (processing worksheet is useless) :​

Sub Demo1()
  Const D = "¤"
    Dim R&, Rg(1) As Range, L&, C%, N%, B%, F&
        R = 1
        UsedRange.Clear
        Application.ScreenUpdating = False
        [A1:G1] = [{"Mentee ID","Mentee Name","Mentor ID","Mentor Name","Programme","Gender","Code"}]
    With Sheets(1).[A1].CurrentRegion
        .Range("J3:K" & .Rows.Count).Formula = Array("=D3&""" & D & """&E3", "=J3&""" & D & """&F3")
         Set Rg(1) = .Parent.UsedRange.Columns
    End With
    With Sheets(2)
        For L = 3 To .[A1].CurrentRegion.Rows.Count
            C = 11:  N = 3
            Set Rg(0) = Rg(1)(C).Find(Join(Application.Index(.Rows(L).Columns("D:F").Value, 1, 0), D), , xlValues)
         If Rg(0) Is Nothing Then
            C = 10:  N = 2
            Set Rg(0) = Rg(1)(C).Find(Join(Application.Index(.Rows(L).Columns("D:E").Value, 1, 0), D))
             If Rg(0) Is Nothing Then
                C = 5:  N = 1
                Set Rg(0) = Rg(1)(C).Find(.Cells(L, 5))
             End If
         End If
            If Rg(0) Is Nothing Then
                R = R + 1
                Rows(R).Columns("A:B") = .Rows(L).Columns("A:B").Value
            Else
                    B = 1 - B
                    F = Rg(0).Row
                Do
                    R = R + 1
                    Rows(R).Columns("A:G").Interior.ColorIndex = 27 + B
                    Rows(R).Columns("A:B") = .Rows(L).Columns("A:B").Value
                    Rows(R).Columns("C:D") = Rg(0).Parent.Rows(Rg(0).Row).Columns("A:B").Value
                    Cells(R, 5).Resize(, N) = "Matched"
                    Set Rg(0) = Rg(1)(C).FindNext(Rg(0))
                Loop Until Rg(0).Row = F
            End If
        Next
    End With
        Application.ScreenUpdating = True
        Erase Rg
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[cheq]

Thanks very much for your effort. I applied it with the test data as attached. I really love how the match was created when all 3 criteria matched.

I would like to see if following items can be made as well.

1) Given the same mentor (e.g.,13456789 Handscomb appeared 26 times) appears next to the names of mentees multiply. Any idea of how can I control the mentor-mentee ratio? I guess I can use pivot table and manually monitor the ratio, any better way? I can set a specific ratio, like 1 mentor to 14-15 mentees.

2) For those with 2/1 matches (For example, in row 3-11, 9 mentors matched 2 criteria with the mentee-60001112 Harv), can I further use excel function or VBA to help me select the matching (Condition: a mentor can match with maximum of 15 mentees)? I attempted the use of removing duplicates, but I could not control the mentor-mentee ratio unless I manually remove the data of mentors when it has matched with 15 mentees already.

3) For the remaining mentees (All criteria are unmatched, starting in row 1897), how can I pair them with mentors (who have less than 15 mentee) except manually matching it?

It's fine if certain operation needs to perform manually. I hope to make it in Macro/formulae as much as possible to reduce human's errors (e.g., typo).

Really appreciate for your help! You are super helpful!

 

[Marc L]

This demonstration is just the beginning of the final procedure.​

But before to go further I have to find out another thread​

where I wrote something similar in order to not reinvent the wheel with the risk of a square one !​

​

 

[vletm]

cheq
I checked Your original 'my file' again.
You've used one old sample version.
It was good that - now You could give enough data to test with more realistic data.
Your data has 'only' three criteria and original case has much more.
The original logic was totally different than with Your case.
I modified and cleaned some parts of code based the latest version of that sample.
I left logic same.
Now it looks like this.

 

[cheq]

Thank you so much! I do not have my computer with me now, will test it with my real data shortly.

Have a good day!

 

[Marc L]

will test it with my real data shortly.

I wait for your return according to vletm's code to know if it is fast enough before to go further …​

​

 

[cheq]

cheq
I checked Your original 'my file' again.
You've used one old sample version.
It was good that - now You could give enough data to test with more realistic data.
Your data has 'only' three criteria and original case has much more.
The original logic was totally different than with Your case.
I modified and cleaned some parts of code based the latest version of that sample.
I left logic same.
Now it looks like this.

Thank you very much for modifying the code for my case specifically. I tried the code with the real and fake data. It ran effectively and completed in just 3 mins, which is awesome. The option of setting "Mentees per Mentors" in the Mentors sheet and the two result sheets (Mentors - Mentees & Mentees - Mentors) look fantastic to me.

The only thing left I guess is the logic. I understood the logic is the same as the previous one, thus it is not perfectly fit my purpose. I would like to have at many as pairs with all three criteria matched. In current logic, just 4 pairs of mentor-mentee with 3 criteria matched were established.

Also, I wonder if I can see the result of the matching criteria on the "Mentees - Mentors" page (Column N,O,P & U).

(Similiar to this screenshot)

 

[cheq]

I wait for your return according to vletm's code to know if it is fast enough before to go further …​

​

Yes, vletm's code is efficient and it is fast with my real data. I am very grateful for your willingness to go further.

 

[Marc L]

3) For the remaining mentees (All criteria are unmatched, starting in row 1897), how can I pair them with mentors (who have less than 15 mentee) except manually matching it?

Can't pair with mentors 'cause your post #18 attachment has column G empty except for an unique mentor !​

Attach at least a sample with this column filled according to your real need …​

vletm's code is efficient and it is fast with my real data

How fast ? As my way could be fast …​

​