I think there's an easier way to do this than writing code, as much as I love
doing that.

I'm assuming that your names are in columns A, B and C on both sheets, and
that columns D and E are available for us to work with. Presumed that lists
start on row 2.

In D2 of each workbook/worksheet enter this formula:
=A2 & " " & B2 & " " & C2
then fill that down all the way on both sheets. That will combine your
names into a single test string that we can examine easily with a VLOOKUP()
formula.

In E1 of the first workbook, enter this formula:

=IF(ISNA(VLOOKUP(D2,'[Names2.xls]Other Names
List'!$D$2:$D$5001,1,FALSE)),"YES","")

that shouldn't have a line break in it, of course. As you can see I named
my second workbook Names2.xls and the sheet in it is named "Other Names
List". Any names in this workbook not appearing in the other one will show
"YES" in column E.

Now, over in the other workbook, after setting up the =A2 & " " & B2 & " " &
C2
formula and filling it down, set up a similar VLOOKUP() formula that refers
back to the list in the other workbook, it would look something like this:
=IF(ISNA(VLOOKUP(D2,'[Names1.xls]My Names
List'!$D$2:$D$5001,1,FALSE)),"YES","")

Now for the most part you're going to end up with duplicate "YES" entries
for names that don't match across the workbooks, but by putting the VLOOKUP()
into both books, you also get YES if a name doesn't exist at all in the other
book.

Hope this helps some.



"Yossy" wrote:

I have 5000 rows of first name, middle name and Last name in two different
workbook. How do I compare the names in the first workbook with the other
workbook and create a "Yes" response in another column next to those not
found in the other work book.
Some names have middle name in one work book and the same name may not have
middle name in the other workbook. Can macro do this? if so that will be
awesome..

PLEASE HELP ME, I WILL REALLY APPRECIATE IT, THANKS A LOT