Search and Extract text from a list of description

Hi everyone!

I'm not at all good at creating macros, but I was wondering if anyone has something which might be able to help me with a project I'm working on. Basically, I'll be auditing several descriptions from a database. I'd like to search if a client from a list in the Client tab was mentioned in any of the description found in the Lite tab of the worksheet. Once found in the description, the matching client name should be written under the Client Name column in the Lite tab. I've attached the file for reference. Needing urgent help for this one if possible.

Thanks and appreciate any insight!

Hi!

It should look something like the attached in the Sample tab. So if it does not find either an exact match or partial match to any of the client name, it should leave the Client Name column in the "Lite" tab blank and if it's able to find a match, it should populate the Client Name column with the matching word.

Thanks so much!

Why Federal Express; FedEx in row 3?

Sub test()
    Dim a, i As Long, myPtn As String, m As Object, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With Sheets("client")
        myPtn = Join(Application.Transpose(.Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value), Chr(2))
    End With
    With Cells(1).CurrentRegion.Resize(, 2)
        .Columns(2).Offset(1).ClearContents
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "([$()^\-|{}\[\]+*?.])"
            myPtn = Replace(.Replace(myPtn, "\$1"), Chr(2), "|")
            .Pattern = "\b(" & myPtn & ")\b"
            For i = 2 To UBound(a, 1)
                For Each m In .Execute(a(i, 1))
                    dic(m.Value) = Empty
                Next
                If dic.Count Then a(i, 2) = Join(dic.keys, "; ")
                dic.RemoveAll
            Next
        End With
        .Value = a
    End With
End Sub

Hey, thanks for the macro. I'll try to run this...I'll be including FedEx as a variation to how Federeal Express Corporation is entered in the description. Some marketing description use abbreviations so I would also need to include that in the list. I appreciate your reply and will let you know how it works

Hi,

I tried running the macro and it works like how i want it to but it's unable to pick up some of the client names like abbott, l'oreal, the body shop etc...

Is it because the actual file which I'm using the macro on has more than 9000 entries? so sorry...i was unable to attache the entire list because of the limitations in the allowable file size...

If you are talking about the file other than the one you have uploaded, I have no idea.

Just upload the data and the list that hasn't been picked up.
You don't need to upload entire file and I don't even see it at all...

The macro stops when there's a blank field cell in the marketing description column so I just populated it with a random word for now

sorry...i think i know what the problem is now...thanks so much! i appreciate you taking the time to respond to my questions!!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.