Finding any words from B1:B400 within A1:5000

[ezharris]

Take a look at whiat I am trying to do, any input would be appreciated! (keep scrolling down, i dont know how i got all that extra space! LOL)

<TABLE border=1>
<TBODY><TR>
<TH>CONTRIBUTORS</TH>
<TH>LAST NAMES</TH>









</TR>
<TR>
<TD>JOHN PAUL GLADSTONE</TD>
<TD>PETERSON</TD><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>









</TR>
<TR>
<TD>RALPH JONES</TD>
<TD>MURPHY</TD>







</TR><o></o>
<o></o>
</TR>
<TR>
<TD>ANGELA F. LOGGINS</TD>
<TD>JONES</TD>









</TR>

<o></o>
<o></o>
</TR>





<TR>
<TD>STACEY GIBBENS-STERN</TD>
<TD>STONE</TD>









</TR>

<o></o>
<o></o>
</TR>





<TR>
<TD>JO ANNE MC DERMOTT</TD>
<TD>KOHLER</TD>









</TR>

<o></o>
<o></o>
</TR>





<TR>
<TD>WOLFRAM & HART, LLC.</TD>
<TD>STEPHENS-LOGGINS</TD>









</TR>
















</TABLE>


​

column 1 has a list of names that are not all 'first last' names (cant split up)

Column 2 contains a list of last names only (unique vaules). I am trying to somehow find similarities between the two lists, Ideally, I would like to highlight the common names from column2 in column 1.
I am not too versed in VBA, but am willing to give it a shot if what I am trying to accomplish cannot be done with formulas alone.
Also, if possible, in the instance of GLADSTONE and STONE, i would prefer that not be 'marked'. but can deal with it if needed. (my full list of column 1 is about 5000 names, and I also have an identifier next to each name in a seperate column)

THANK YOU IN ADVANCE FOR ANY INSIGHT!!!!

 

[venkat1926]

see sheet 1 below which is your database
the entries in column B is copied in column A of sheet2(sheet 2 also is given below)
now run this macro and see what happens . see both sheet 1 and sheet2. what you see in sheet 1 and sheet 2 are after running the macro test. you can rerun the macro test.

there are two macroS test, undo. the macro "undo" in embedded in macro "test"
You have to copy paste both the macros in the standard module.
and run the macro "test" ONLY

POST FEEDBACK
The macro is

Sub TEST()
Dim rng As Range, c As Range, cfind As Range
undo
On Error Resume Next
With Worksheets("sheet2")
Set rng = Range(.Range("a2"), .Range("a2").End(xlDown))
For Each c In rng
With Worksheets("sheet1").Columns("a:a")
Set cfind = .Cells.Find(what:=c.Value, lookat:=xlPart)
If Not cfind Is Nothing Then
cfind.Interior.ColorIndex = 3
cfind.Copy c.Offset(0, 1)
End If
End With 'sheet1 column A
Next
End With 'sheet 2
End Sub

Sub undo()
Worksheets("sheet1").Cells.Interior.ColorIndex = xlNone
Worksheets("sheet2").Columns("B:B").Delete
End Sub

ezharris.xls
	A	B	C	D
1	CONTRIBUTORS	LASTNAMES
2	JOHNPAULGLADSTONE	PETERSON
3	RALPHJONES	MURPHY
4	ANGELAF.LOGGINS	JONES
5	STACEYGIBBENS-STERN	STONE
6	JOANNEMCDERMOTT	KOHLER
7	WOLFRAM&HART,LLC.	STEPHENSLOGGINS
Sheet1

ezharris.xls
	A	B	C	D
1	LASTNAMES
2	PETERSON
3	MURPHY
4	JONES	RALPHJONES
5	STONE	JOHNPAULGLADSTONE
6	KOHLER
7	STEPHENSLOGGINS
8
Sheet2

 

[wsjackman]

assumng data is in a2:b??

Excel Workbook
	A	B
2	JOHN PAUL GLADSTONE	PETERSON
3	RALPH JONES	MURPHY
4	ANGELA F. LOGGINS	JONES
5	STACEY GIBBENS-STERN	STONE
6	JO ANNE MC DERMOTT	KOHLER
7	WOLFRAM & HART, LLC.	STEPHENS-LOGGINS
Sheet2

Excel Workbook
	C	D
2
3		Match A
4	Match B
Sheet2