Find Specific Text in a Cell

[madab]

Hi,


Any ideas how to get around this problem … I have 2 lists ofnames. One list is First Name and Last Name, the other list is Title First NameLast Name. I want to identify who is on both lists as best I can.


Any ideas how to do this in excel? I appreciate there maynot be a 100% accurate way of doing this due to multiple people having the samename e.g. John Smith etc. however any suggestions welcome to help me narrow thelist down would be appreciated.





Thanks

 

[steve the fish]

How you approach this depends on how the lists are arranged. Are the names in seperate cells? Same cells? Do they have carriage returns or any special characters? Is there potential for trailing spaces?

 

[madab]

Hi,

The names would be in one cell with a space between thefirst name and surname. There are no special characters and there shouldn’t beany trailing spaces other than the space between the first and surnames.

 

[steve the fish]

Heres a couple that should get you going. The shorter name ie the one without the title is in A1 and the list to search in column B:

=MATCH("*"&A1&"*",B:B,0)
=COUNTIF(B:B,"*"&A1&"*")

 

[Fluff]

If you're interested in a VBA solution.

Sub finddupe()
   Dim cl As Range
   Dim x As String
   Dim Sp
   
   With CreateObject("scripting.dictionary")
      For Each cl In Range("G2", Range("G" & Rows.Count).End(xlUp))
         .Item(cl.Value) = Empty
      Next cl
      For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If .exists(cl.Value) Then x = x & "|" & cl.Value
      Next cl
   End With
   Sp = Split(Replace(x, "|", "", 1, 1), "|")
   Range("K2").Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
End Sub

This assumes your lists are in cols G & A, and will output in col K