• 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 (With large test data)

In fact I deleted my previous post 'cause I did not well see your issue …​
​
cheq said:
(For example, the school code is not matched, but it still shows a black text color "code".
Click to expand...
Yes your bad according to your logic as you forgot Programme & Code …​
… and even adding this new step it may not 'work' because of Gender & Programme before !​
​
 
I'm not saying the matching is problematic, what I mean is the text color, which can help me to identify the criteria.

I believe:

If "Code" of mentor & mentee are matched, the text color of the code number should be in Black colour,

If "Code" of mentor & mentee are unmatched, the text will be in grey colour,
 
Last edited by a moderator:
Yes that's it and on my side no issue with your post #73 attachment, result as expected according to your logic :​
​
cheq 01 .jpg​
 
Questions :​
according to the priority level, what is the most important criteria alone : Gender, Programme or Code ?​
And which is the one at rank #2 ?​
​
 
Glitch corrected in post #27, try the last version …​
​
Question : is it only under Windows version ?​
​
 
Update: When I try it with an Excel for Mac Version: 16.60 (22041000), it works perfectly. I can use the macOS to generate the data, so It may not be an issue anymore. Many thanks!

With the last version of the #27 code, still encounter the same problem (Text color shown as black, but not grey for unmatched "Code"). I used Excel 2019 64 bit, version: 1808 (Build 10357.20081) and attached the file.
 

Attachments

  • Marc L with Fake data_v4.xlsm
    48.2 KB · Views: 11
Last edited:
cheq
If You still would like to get different variations by own selection of results then test this.
There are some blue fonts.
 

Attachments

  • Fake data.xlsb
    250.5 KB · Views: 6
Thank you so much!

Different variations can help me identify more matching combinations. It is definitely helpful and fits my needs!! Will test it shortly!
 
cheq said:
With the last version of the #27 code, still encounter the same problem (Text color shown as black, but not grey for unmatched "Code"). I used Excel 2019 64 bit, version: 1808 (Build 10357.20081) and attached the file.
Click to expand...
According to your last attachment it seems to well work on my side under Windows & Excel 2010 32 bits version …​
… or I'm missing somethin', if you could attach a workbook with the non matching 'Code' in a yellow background for example.​

cheq said:
When I try it with an Excel for Mac Version: 16.60 (22041000), it works perfectly. I can use the macOS to generate the data, so It may not be an issue anymore. Many thanks!
Click to expand...
As it well works under Mac - hoping with the same test workbook - like on my side under Windows​
it seems it should be more a specific issue of your computer …​
​
The post #27 demonstration just uses Excel basics like any Excel beginner operating manually.​
I have other ways which could be four times faster but specific to Windows version …​
One can be adapted to Mac version - working too under Windows - but as Excel for Mac could be slower than under Windows​
I don't know if it's worth the time …​
​
 
I will try the data again with the Windows Excel 2019 tomorrow. If the problem still happens, I will highlight them and share the file here.

I am totally satisfied with the current processing time. Thank you for the thoughtful suggestion and no need to invest extra time on it. ^_^
 
According to your post #85 attachment try to convert the Mentees worksheet Code column to numeric like in Mentors worksheet …​
​
 
I believe it's my random computer problem. I download #85 attachment, and it works this time.

#Code column to numeric to match the data type: Will do it, thanks for the advice!!
 
In fact as now it works you do not ever need to convert the column to numeric even for a faster way​
but all I must know is if you need a Windows version only or a VBA procedure running whatever on Mac like under Windows …​
​
 
​
For the Mac / Windows fast way start with the first two steps of this thead :​

A Dictionary for Mac & Windows …

Hello, this is a dictionary like the Windows ActiveX Scripting.Dictionary working whatever on Mac or under Windows with more features just with VBA basics ! First download the below text file attachment. Once on your local hard drive, rename it just removing the .txt extension in order it's...
chandoo.org
​
Then according to your attachments paste this new VBA demonstration only to the top of a brand new result worksheet module :​
Code: 
Const C = 16
  Dim Rc As Range, V, oCol As New Collection

Sub ColDic(T$)
  Const D = "¤", S = "&""" & D & """&"
    Dim K, R&, W, oDic As New MDictionary, A, X, M&, N&
        K = Evaluate("{" & T & "}-1")
        For R = 1 To UBound(K):  K(R) = Rc(K(R)).Address:  Next
        K = Rc.Parent.Evaluate(Join(K, S))
    For R = 1 To Rc.Rows.Count
        If V(R, 1) Then
            W = oDic(K(R, 1))
            If IsArray(W) Then ReDim Preserve W(UBound(W) + 1): W(UBound(W)) = R Else W = Array(R)
            oDic(K(R, 1)) = W
        End If
    Next
        K = Evaluate("{" & T & "}")
        A = K
    With UsedRange.Rows(Cells(Rows.Count, 3).End(xlUp)(2).Row & ":" & UsedRange.Rows.Count).Columns
            For R = 1 To UBound(K):  K(R) = .Item(K(R)).Address:  Next
            K = Evaluate(Join(K, S))
        For R = 1 To .Rows.Count
            If oDic.Exists(K(R, 1)) Then
                   W = oDic(K(R, 1))
                   X = Application.Index(V, W)
                   M = Application.Max(X)
                If M Then
                    N = W(Application.Match(M, X, 0) - 1)
                    V(N, 1) = M - 1
                   .Item("C:D").Rows(R) = oCol(N)
                   .Cells(R, .Count) = 1
                Else
                    oDic.Remove K(R, 1)
                End If
            End If
        Next
        If N Then
           .Sort .Item(.Count), 1, Header:=2
            R = Cells(Rows.Count, 3).End(xlUp).Row
            K = [{5,6,7}]
            A = Application.Match(K, A, 0)
            For N = 1 To UBound(A):  A(N) = IIf(IsError(A(N)), Range(Cells(.Row, K(N)), Cells(R, K(N))).Address, D):  Next
            A = Join(Filter(A, D, False), ",")
            If A > "" Then Range(A).Font.ColorIndex = C
        End If
    End With
End Sub

Sub Demo2()
    Dim R&, N&
        UsedRange.Clear
    With Sheet1
        R = .[IF(ISNUMBER(G3),G3,0)]:  If R < 1 Then Beep: Exit Sub
        Set Rc = .[A1].CurrentRegion.Rows("3:" & .[A1].CurrentRegion.Rows.Count).Columns
        V = .Evaluate(Replace("IF(ISNUMBER(#),#," & R & ")", "#", Rc(1)(7).Address))
    End With
        For R = 1 To Rc.Rows.Count:  oCol.Add Rc("A:B").Rows(R).Value:  Next
        Application.ScreenUpdating = False
        [A1:H1] = [{"Mentee ID","Mentee Name","Mentor ID","Mentor Name","Gender","Programme","Code"," "}]
    With Sheet2.[A1].CurrentRegion.Rows
        .Range("A3:B" & .Count).Copy [A2]
        .Range("D3:F" & .Count).Copy [E2]
    End With
        ColDic "6,7,5"          ' Programme & Code & Gender
        ColDic "6,7"            ' Programme & Code
        ColDic "6,5"            ' Programme & Gender
        ColDic "6"              ' Programme
        ColDic "7,5"            ' Code & Gender
        ColDic "7"              ' Code
        ColDic "5"              ' Gender
        R = Cells(Rows.Count, 3).End(xlUp)(2).Row
    With UsedRange.Rows
        If R <= .Count Then     ' last mentors to last mentees
                .Item(R & ":" & .Count).Columns("E:G").Font.ColorIndex = C
            For R = R To .Count
                N = Application.Match(Application.Max(V), V, 0)
                If V(N, 1) Then V(N, 1) = V(N, 1) - 1 Else Exit For
               .Item(R).Columns("C:D") = oCol(N)
            Next
        End If
           .Columns(.Columns.Count).Clear
    End With
        Application.ScreenUpdating = True
        Set Rc = Nothing: Set oCol = Nothing: Erase V
End Sub
You may Like it !​
​
 
If you wanna compare this new demonstration results with the post #27 demonstration​
then you must remove in the post #27 Check procedure the red part of this codeline :​
​
If V(0)(Rg(0).Row, 1) > M Then M = V(0)(Rg(0).Row, 1): N = Rg(0).Row Else If M And V(0)(Rg(0).Row, 1) = M Then Exit Do​
​
 
cheq
One data and eg two different sample results.
There could get more variations by user selections.
... but which one would be the most correct or even useful?
 

Attachments

  • Fake data - ver1.pdf
    80.7 KB · Views: 5
  • Fake data - ver2.pdf
    80.2 KB · Views: 2
vletm said:
cheq #71

# Do You still hope to get that Gender-case to be more useful?
Click to expand...

After testing with more data, I don't think I need the Gender-case because Marc L's version can cover this part (I know the code logic and the output result can be different, but it's good enough in my situation).

Credit to both [Marc L] & [vletm], the solution to my Mentor-mentee matching problem is beyond my expectation. I am so glad to come up with more than 1 working method and let me see the difference in the result. Thank you for all your hard and generous work.
 
ColDic procedure updated in post #94 in order to earn some milliseconds …​
​
How fast is Demo2 on your side, whatever on Mac or under Windows ?​
​
 
Just used the #94 code to test again, I'm sorry that I could not count the exact time.
The result just appears within 1 second.:eek:

~My data set (1:10 ratio): 98 mentors to 973 mentees
 
cheq
# Gender-case
Original: eg Mentees Male try to match with Male Mentors or Mentees Female try to match with Female..
My written: eg Mentees Male try to match with Female or Mentees Female try to match with Male.
# Logic
You may want to get different results depending on what details you want to highlight.
 
You must log in or register to reply here.
Back
Top