Consulting

Results 1 to 9 of 9

Thread: If Cell Contains Any Words Found in the Adjacent Cell

  1. #1
    VBAX Regular
    Joined
    Apr 2017
    Posts
    77
    Location

    Question If Cell Contains Any Words Found in the Adjacent Cell

    Please point me in the right direction. I don't know which formula(s) to use to get this output:

    If A2 matches to any of the abbreviated roles in B2 then "Match" if not "No Match"

    Not an exact match, but as long as column B has a minimum of 1 match, then it is a match.

    A B
    Role VerifyRole
    PC PC, OD, PO Match
    PO PO, PO Match
    OD PSS, OD Match
    PSS PO, PSS Match
    OD, PC PC, OD Match
    OD, PC PO, PSS No Match
    OD, PO OD, PC, PC, PO Match
    Last edited by jnix612; 04-14-2023 at 08:27 PM.

  2. #2
    VBAX Regular
    Joined
    Apr 2017
    Posts
    77
    Location
    I am editing this message because I thought I found the answer. I was wrong.

  3. #3
    can you use VBA?
    Attached Files Attached Files

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Maybe you could try this as well

    Private Sub CheckText()
    Dim Partial_text As String
    Dim myrange As Range
    Partial_text = Worksheets("Sheet1").Cells(1, 2).Value
    Set myrange = Worksheets("Sheet1").Cells(2, 2).Value
    For Each cell In myrange
       If InStr((cell.Value), (Partial_text)) <> 0 Then
           ActiveCell(Offset(0, 1).Value) = "Match"
           Else
           ActiveCell(Offset(0, 1).Value) = "No Match"
       End If
    Next
    End Sub
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    VBAX Regular
    Joined
    Apr 2017
    Posts
    77
    Location
    Aussiebear, this also worked. Thank you.
    Last edited by jnix612; 04-15-2023 at 08:34 AM.

  6. #6
    VBAX Regular
    Joined
    Apr 2017
    Posts
    77
    Location
    arnelgp yes and this worked. Thank you.

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Another way would use a User Defined Function which would be more flexible

    Capture.JPG


    Option Explicit
    
    
    Function VerifyRole(Role As String, Verify As String) As String
        Dim vR As Variant, vV As Variant
        Dim i As Long, j As Long
        
        VerifyRole = "Match"
        
        vR = Split(Role, ",")
        vV = Split(Verify, ",")
    
    
        For i = LBound(vR) To UBound(vR)
            For j = LBound(vV) To UBound(vV)
                If Trim(vR(i)) = Trim(vV(j)) Then Exit Function
            Next j
        Next i
    
    
        VerifyRole = "NoMatch"
    
    
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    also this one:
    Public Function fnIsMatched(ByVal a As Variant, b As Variant) As Boolean
    Dim var1 As Variant, var2 As Variant
    Dim i As Integer, tf As Boolean
    If IsNull(a) Or IsNull(b) Then
        Exit Function
    End If
    a = Replace$(a, " ", "")
    b = Replace$(b, " ", "")
    var1 = Split(a, ",")
    var2 = Split(b, ",")
    For i = 0 To UBound(var1)
        tf = UBound(Filter(var2, var1(i))) > -1
        If tf Then
            Exit For
        End If
    Next
    fnIsMatched = tf
    End Function

  9. #9
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    An Excel 365 formulated option:
    =LET(a,BYROW(A2:B8,LAMBDA(x,SUM(SEARCH(TEXTSPLIT(INDEX(x,,1),", "),INDEX(x,2))))),IF(ISNUMBER(a),"Match","No Match"))
    Attached Files Attached Files
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •