Is there a way to exact match when using filter on an array?

Question

I am new to VBA and am trying to create a user-defined function in VBA that determines if there's a title and gender mismatch. E.g.: if the title is "Mr" and the gender is "F" then it'll return a Boolean of TRUE (there is a mismatch).

However, when the gender is "F" and the title is "MR", it keeps returning FALSE, as MR is a substring of MRS. Is there a way to ensure that it does an exact match?

Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean

title = UCase(trim(title))

gender = UCase(trim(gender))

If gender = "M" And UBound(Filter(Array("MR", "DR"), title)) = -1 Then

GenderTitleMismatch = True

ElseIf gender = "F" and Ubound(Filter(Array("MRS","MS","DR","MISS"), title)) = -1 Then

GenderTitleMismatch = True

Else

GenderTitleMismatch = False

End If

End Function

Filter always checks substrings, so use a loop instead. Eg: https://stackoverflow.com/a/38268261/478884 — Tim Williams, Feb 06 '23 at 06:58
If you are doing lots of array manipulation of this sort I recommend this VBA library from GitHub: [VBA-ArrayTools](https://github.com/cristianbuse/VBA-ArrayTools). — GWD, Feb 06 '23 at 10:25

CLR · Answer 1 · 2023-02-06T09:19:19.137

You could still use your method and prevent substring matches by forcing a separator:

Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean

    title = UCase(Trim(title)) & "/"
    gender = UCase(Trim(gender))
    
    GenderTitleMismatch = False
    
    If gender = "M" And UBound(Filter(Array("MR/", "DR/"), title)) < 0 Then
        GenderTitleMismatch = True
    End If
    
    If gender = "F" And UBound(Filter(Array("MRS/", "MS/", "DR/", "MISS/"), title)) < 0 Then
        GenderTitleMismatch = True
    End If

End Function

But you could simplify it further like so:

Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean

    Dim srch As String: srch = UCase(Trim(title)) & "/" & UCase(Trim(gender))
    
    Dim valid(): valid = Array("MR/M", "DR/M", "MRS/F", "MS/F", "DR/F", "MISS/F")

    GenderTitleMismatch = UBound(Filter(valid, srch)) < 0

End Function

score 0 · Answer 2 · answered Feb 06 '23 at 11:17

Title Gender Mismatch

Function TitleGenderMismatch(ByVal Title, ByVal Gender) As Boolean
    
    Dim Titles(): Titles = Array("mrm", "drm", "mrsf", "msf", "missf", "drf")
    
    Dim tStr As String: tStr = Trim(CStr(Title))
    'tStr = Replace(tStr, ".", "") ' remove the dot: e.g. USA, Canada...
    ' Allow title with name e.g. 'Mr John Smith'.
    Dim tSpacePosition As Long: tSpacePosition = InStr(tStr, " ")
    If tSpacePosition > 0 Then tStr = Left(tStr, tSpacePosition - 1)
    
    Dim gStr As String: gStr = Trim(CStr(Gender))
    ' Allow words: e.g. 'male' and 'feemale' instead of 'm' and 'f'.
    gStr = Left(gStr, 1)
    
    Dim IsMisMatch As Boolean ' '.Match' is case-insensitive
    IsMisMatch = IsError(Application.Match(tStr & gStr, Titles, 0))
    
    TitleGenderMismatch = IsMisMatch

End Function

Is there a way to exact match when using filter on an array?

2 Answers2

Title Gender Mismatch