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

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

Thanks JLatham,
Your formula is good. However, the problem is that some middle name are 1
character and the other duplicate could be more than 1 character e.g Don R
Hans and Don Richard Hans. So even when I combine into a single test string
It is still hard to tell which are duplicate entries.

Please help me. I have been thinking really hard on the best possible
senario to handle this situation.
Thanks
"JLatham" wrote:

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

The code to do the fuzzy logic you're asking for is kind of tough. I
actually started putting some together and then hit upon this idea and
abandonded the code.

Can you give me some definite rules as to what names you want to get
"flagged" for closer scrutiny? Something like one or more of these, perhaps:

If first, middle and last are exactly same, ignore.

If first and last are same, but middle name is different, BUT one is single
letter and other is longer and the longer starts with same letter then flag
it as "YES" or "middle name possibly same" indicator?

If no match to first & last (or complete) name is found, then mark it as
something like "Not in other list"?

A firm set of rules to begin coding against would be a real help.

A problem that you'll probably have to deal with 'manually' would be
duplicate identical names, but that are really 2 (or more) different people,
like multiple "Mary Smith"s or "John Brown"s in the same list.

I think we can whittle down the number of names you have to deal with by
eyeball, but the vagaries of names is not going to let us handle this as well
as we could deal with definitely unique entries or numbers.



"Yossy" wrote:

Thanks JLatham,
Your formula is good. However, the problem is that some middle name are 1
character and the other duplicate could be more than 1 character e.g Don R
Hans and Don Richard Hans. So even when I combine into a single test string
It is still hard to tell which are duplicate entries.

Please help me. I have been thinking really hard on the best possible
senario to handle this situation.
Thanks
"JLatham" wrote:

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

Oh, and how solid is the assumption that
Don R Hans is the same person as Don Richard Hans,
might Don R Hans be Don Robert Hans (with there also being a Don Richard
Hans in that other list), or even Donald R Hans in the other list?


"Yossy" wrote:

Thanks JLatham,
Your formula is good. However, the problem is that some middle name are 1
character and the other duplicate could be more than 1 character e.g Don R
Hans and Don Richard Hans. So even when I combine into a single test string
It is still hard to tell which are duplicate entries.

Please help me. I have been thinking really hard on the best possible
senario to handle this situation.
Thanks
"JLatham" wrote:

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

thanks JLatham,
that is exactly the problem am facing. am trying to seperate existing names
from the tow sheets of first name last name and or middle name and also bring
out names that are not included in the existing sheet (which is from database
record.)
One sheet is a new data and the other is existing. Am way confused and lost
literarily.

Some names might even be First and last name while there will be first
middle and last name in the other sheet.

All your help will be absolutely appreciated.

"JLatham" wrote:

Oh, and how solid is the assumption that
Don R Hans is the same person as Don Richard Hans,
might Don R Hans be Don Robert Hans (with there also being a Don Richard
Hans in that other list), or even Donald R Hans in the other list?


"Yossy" wrote:

Thanks JLatham,
Your formula is good. However, the problem is that some middle name are 1
character and the other duplicate could be more than 1 character e.g Don R
Hans and Don Richard Hans. So even when I combine into a single test string
It is still hard to tell which are duplicate entries.

Please help me. I have been thinking really hard on the best possible
senario to handle this situation.
Thanks
"JLatham" wrote:

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

This is an area that doesn't fit well with processing. I think in the end
the best we can do is first identify absolute definite matches on all 3 parts
of the name, and then make some best guesses and present a list of
"here's what's on one sheet" ... "here's what may match on the other sheet"
entries
and a list of
"here's what's on a sheet with pretty much nothing I think is even close on
the other"

and let you work from there with those two lists.


"Yossy" wrote:

thanks JLatham,
that is exactly the problem am facing. am trying to seperate existing names
from the tow sheets of first name last name and or middle name and also bring
out names that are not included in the existing sheet (which is from database
record.)
One sheet is a new data and the other is existing. Am way confused and lost
literarily.

Some names might even be First and last name while there will be first
middle and last name in the other sheet.

All your help will be absolutely appreciated.

"JLatham" wrote:

Oh, and how solid is the assumption that
Don R Hans is the same person as Don Richard Hans,
might Don R Hans be Don Robert Hans (with there also being a Don Richard
Hans in that other list), or even Donald R Hans in the other list?


"Yossy" wrote:

Thanks JLatham,
Your formula is good. However, the problem is that some middle name are 1
character and the other duplicate could be more than 1 character e.g Don R
Hans and Don Richard Hans. So even when I combine into a single test string
It is still hard to tell which are duplicate entries.

Please help me. I have been thinking really hard on the best possible
senario to handle this situation.
Thanks
"JLatham" wrote:

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