locked
Need Function to compare two columns for matching SUBSTRING values RRS feed

  • Question

  • Need Function to compare two columns for matching SUBSTRING values. Let me explain. I have column A and column B. They both will have some text. But they will both have "m1" or "m2" or "m3" ... up to "m25"  somewhere in them. I want to find the mismatches.

    Is this small example I am interested in identifying row 3. Column A has M12, but Column B has M1. This is a mismatch. They both need to contain the same M99 value to be a match.

    Column A,B

    HBI_M2, TableM2
    HBI_M3_xx, TableM3
    HBI_M12, TableM1

    Can someone help me get started on this? I am not too good with Excel Functions. Thanks.

     
    Friday, July 24, 2015 2:39 PM

Answers

  • Create the following function in a module in the Visual Basic Editor:

    Function Extract(s As String) As String
        Dim p As Long
        Dim q As Long
        p = InStr(s, "M")
        If p > 0 Then
            q = p
            Do While q < Len(s)
                If IsNumeric(Mid(s, q + 1, 1)) Then
                    q = q + 1
                Else
                    Exit Do
                End If
            Loop
            Extract = Mid(s, p, q - p + 1)
        End If
    End Function

    Enter the following formula in C1:

    =Extract(A1)=Extract(B1)

    and fill down to the end of the data. You'll get TRUE if the Mnn values match, FALSE otherwise.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Friday, July 24, 2015 4:00 PM
    • Marked as answer by TheBrenda Saturday, July 25, 2015 6:40 PM
    Friday, July 24, 2015 3:58 PM

All replies

  • Create the following function in a module in the Visual Basic Editor:

    Function Extract(s As String) As String
        Dim p As Long
        Dim q As Long
        p = InStr(s, "M")
        If p > 0 Then
            q = p
            Do While q < Len(s)
                If IsNumeric(Mid(s, q + 1, 1)) Then
                    q = q + 1
                Else
                    Exit Do
                End If
            Loop
            Extract = Mid(s, p, q - p + 1)
        End If
    End Function

    Enter the following formula in C1:

    =Extract(A1)=Extract(B1)

    and fill down to the end of the data. You'll get TRUE if the Mnn values match, FALSE otherwise.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Friday, July 24, 2015 4:00 PM
    • Marked as answer by TheBrenda Saturday, July 25, 2015 6:40 PM
    Friday, July 24, 2015 3:58 PM
  • Hans, your post definately got me started. I did not use EXTRACT as the function name, that seemed to be a problem -  maybe a reserved word? There were also a couple of other gotchas. This is what I ended up with.

    =IF(PPExtract(INDIRECT("$A" & ROW()))=PPExtract(INDIRECT("$B" & ROW())),  PPExtract(INDIRECT("$A" & ROW())),  PPExtract(INDIRECT("$A" & ROW())) & "/" & PPExtract(INDIRECT("$B" & ROW())))

    Public Function PPExtract(s As String) As String
        Dim p As Long
        Dim q As Long
        Dim freqm As String
        
        freqm = "M"
    
        q = 25
    
        Do While q > 0
            If InStr(s, freqm & CStr(q)) > 0 Then
                If q < 10 Then
                    PPExtract = Mid(s, InStr(s, freqm & CStr(q)), 2)
                    Exit Function
                End If
                PPExtract = Mid(s, InStr(s, freqm & CStr(q)), 3)
                Exit Function
            End If
            q = q - 1
        Loop
        
        PPExtract = "Not Found"
        Exit Function
    
    End Function

    Saturday, July 25, 2015 6:39 PM