C
Christie
I am trying to cross reference two worksheet and if a name appears on both
worksheets a Y would appear in the box.
Can anyone help??
worksheets a Y would appear in the box.
Can anyone help??
P
Pete_UK
You will need to give a bit more detail than this on what you want to
achieve. Which "box" do you refer to? How is your data laid out? Which
column(s) would contain the names? etc.
As a general note, you can have a MATCH formula in a column on one
sheet which looks at the other sheet for an exact match, and a similar
formula in the other sheet to look at the first sheet, so against each
name you can return a "Y" if there is a match - is this what you want?
Pete
achieve. Which "box" do you refer to? How is your data laid out? Which
column(s) would contain the names? etc.
As a general note, you can have a MATCH formula in a column on one
sheet which looks at the other sheet for an exact match, and a similar
formula in the other sheet to look at the first sheet, so against each
name you can return a "Y" if there is a match - is this what you want?
Pete
J
JBeaucaire
Sheet1 has the names you want to check against a second sheet. Sheet
has the other list you want to compare, both sheets have the names i
columnA
On sheet1, in B2 (where you want the Y to appear, we want to check th
namen in A2 against Sheet2 columnA. Let's set the range on sheet2 to 20
rows. The formula in B2 would be
=IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$200,0)),"","Y"
Now copy that formula down to check the rest of the names on Sheet
has the other list you want to compare, both sheets have the names i
columnA
On sheet1, in B2 (where you want the Y to appear, we want to check th
namen in A2 against Sheet2 columnA. Let's set the range on sheet2 to 20
rows. The formula in B2 would be
=IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$200,0)),"","Y"
Now copy that formula down to check the rest of the names on Sheet
C
Christie
Thank you for your reply.
This achieved what I wanted however now I want to see if both the first name
and surname I have on one sheet matches the names on the other sheet and they
are in seperate cells. How would I do this?
This achieved what I wanted however now I want to see if both the first name
and surname I have on one sheet matches the names on the other sheet and they
are in seperate cells. How would I do this?
M
Max
One way
Assuming first names and surnames are in cols A and B in both Sheet1/2
In Sheet1,
Put in C1, normal ENTER
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100=B1),),0)),"Y",""))
Copy down as far as required. Adapt the ranges to suit.
Pl press the YES buttons (like the ones below) in ALL responses which helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
Assuming first names and surnames are in cols A and B in both Sheet1/2
In Sheet1,
Put in C1, normal ENTER
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100=B1),),0)),"Y",""))
Copy down as far as required. Adapt the ranges to suit.
Pl press the YES buttons (like the ones below) in ALL responses which helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
C
Christie
Thank you this helped a great deal however is their a way that I can include
in the formula that if one sheet has a christian name, eg Anna and the other
sheet has both christian and middle name, eg Anna Rose that this will show as
a match? Also to include this in matching address's???
Christie
in the formula that if one sheet has a christian name, eg Anna and the other
sheet has both christian and middle name, eg Anna Rose that this will show as
a match? Also to include this in matching address's???
Christie
M
Max
It becomes decidedly more difficult. A measure of success might be
achieveable using fuzzier search terms within the criteria eg: ...
ISNUMBER(SEARCH(...))
Lets say you have in Sheet2's A1:B1,
data such as: Anna Rose, Mary
(Mary in B1)
And in Sheet1,
you have the lookups
in A1: Anna, in B1: Mary
This revision of the earlier, placed in C1, normal ENTER:
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX(ISNUMBER(SEARCH(TRIM(A1)&"
",Sheet2!A$1:A$100))*(Sheet2!B$1:B$100=B1),),0)),"Y",""))
would return a correct: "Y" for the data in Sheet2. It will avoid returning
a spurious "Y" should you have: Annabel, Mary in Sheet2 (instead of: Anna
Rose, Mary).
Above is still not watertight of course, but the method might help you to
narrow down the desired searches.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
achieveable using fuzzier search terms within the criteria eg: ...
ISNUMBER(SEARCH(...))
Lets say you have in Sheet2's A1:B1,
data such as: Anna Rose, Mary
(Mary in B1)
And in Sheet1,
you have the lookups
in A1: Anna, in B1: Mary
This revision of the earlier, placed in C1, normal ENTER:
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX(ISNUMBER(SEARCH(TRIM(A1)&"
",Sheet2!A$1:A$100))*(Sheet2!B$1:B$100=B1),),0)),"Y",""))
would return a correct: "Y" for the data in Sheet2. It will avoid returning
a spurious "Y" should you have: Annabel, Mary in Sheet2 (instead of: Anna
Rose, Mary).
Above is still not watertight of course, but the method might help you to
narrow down the desired searches.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
M
Max
The earlier " Isnumber(search(...)) " term in the expression should appear
like this:
... ISNUMBER(SEARCH(TRIM(A1)&" ",Sheet2!A$1:A$100))
You probably need to correct the line break when you paste it into the
formula bar
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
like this:
... ISNUMBER(SEARCH(TRIM(A1)&" ",Sheet2!A$1:A$100))
You probably need to correct the line break when you paste it into the
formula bar
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
C
Christie
This does work however I need it to pick up Y if both the christian and the
surname is matches but the other worksheet has a middle name and the other
worksheet doesn't. At the moment with the formula you have given me it picks
up if for eg brown and it would say Y to browne.
Is this possible.
surname is matches but the other worksheet has a middle name and the other
worksheet doesn't. At the moment with the formula you have given me it picks
up if for eg brown and it would say Y to browne.
Is this possible.
M
Max
Could you post some sample data of what you have in Sheet1/2, and your
intents? Clearer that way
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
intents? Clearer that way
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
C
Christie
Some sample posts would be:
Sheet one - Surname: Smith (A1), Christian name: Michael John
Sheet two - Surname: Smith (A1), Christian name: Michael
I would need the above to match even though sheet two does not have the
middle name.
I also have a sheet that has both surname and christian name in the one
cell, eg Smith Michael John (A1) and I need this to match the above sheets.
Hope this makes it easier to understand
Christie
Sheet one - Surname: Smith (A1), Christian name: Michael John
Sheet two - Surname: Smith (A1), Christian name: Michael
I would need the above to match even though sheet two does not have the
middle name.
I also have a sheet that has both surname and christian name in the one
cell, eg Smith Michael John (A1) and I need this to match the above sheets.
Hope this makes it easier to understand
Christie
M
Max
Tough. As a systematic approach to narrow it down, you could concat cols A
and B for both Sheet1/2 in col C, using in C1, copied down: =TRIM(A1&"
"&B1). Then try to match based on this concat col C in both sheets, one
against the other, viz.:
In Sheet1,
In D1, copied down:
=IF(C1="","",IF(ISNUMBER(MATCH(TRUE,INDEX(ISNUMBER(SEARCH(C1,Sheet2!C$1:C$100)),),0)),"Y",""))
In Sheet2,
In D1, copied down:
=IF(C1="","",IF(ISNUMBER(MATCH(TRUE,INDEX(ISNUMBER(SEARCH(C1,Sheet1!C$1:C$100)),),0)),"Y",""))
You could also try these 2 links for more info on fuzzy text match:
http://www.dicks-blog.com/archives/2004/06/16/fuzzy-text-match/
http://j-walk.com/ss/excel/tips/tip77.htm
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
and B for both Sheet1/2 in col C, using in C1, copied down: =TRIM(A1&"
"&B1). Then try to match based on this concat col C in both sheets, one
against the other, viz.:
In Sheet1,
In D1, copied down:
=IF(C1="","",IF(ISNUMBER(MATCH(TRUE,INDEX(ISNUMBER(SEARCH(C1,Sheet2!C$1:C$100)),),0)),"Y",""))
In Sheet2,
In D1, copied down:
=IF(C1="","",IF(ISNUMBER(MATCH(TRUE,INDEX(ISNUMBER(SEARCH(C1,Sheet1!C$1:C$100)),),0)),"Y",""))
You could also try these 2 links for more info on fuzzy text match:
http://www.dicks-blog.com/archives/2004/06/16/fuzzy-text-match/
http://j-walk.com/ss/excel/tips/tip77.htm
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
C
Christie
Thank you this worked out really well.
I still don't know how to include the middle name into the formula but it
has narrowed it down a great deal. If you can think of a way to do this, it
will be greatfully appreciated.
Christie
I still don't know how to include the middle name into the formula but it
has narrowed it down a great deal. If you can think of a way to do this, it
will be greatfully appreciated.
Christie
M
Max
Welcome, glad it helped, ... to the extent possible.
I don't think I've anything further to offer you here
You could try a new, fresh posting to garner ideas from other responders
Cheers
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
I don't think I've anything further to offer you here
You could try a new, fresh posting to garner ideas from other responders
Cheers
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
C
Christie
Thanks Max, will do 
Max said: