Does anyone know how to cross reference two worksheets
 

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??

Does anyone know how to cross reference two worksheets
 

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

On Jan 9, 12:56*am, Christie
wrote:
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??

Does anyone know how to cross reference two worksheets
 

Sheet1 has the names you want to check against a second sheet. Sheet2
has the other list you want to compare, both sheets have the names in
columnA.

On sheet1, in B2 (where you want the Y to appear, we want to check the
namen in A2 against Sheet2 columnA. Let's set the range on sheet2 to 200
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 Sheet1


Christie;174798 Wrote:
I am trying to cross reference two worksheet and if a name appears on
both
worksheets a Y would appear in the box.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48360

Does anyone know how to cross reference two worksheets
 

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?

"JBeaucaire" wrote:


Sheet1 has the names you want to check against a second sheet. Sheet2
has the other list you want to compare, both sheets have the names in
columnA.

On sheet1, in B2 (where you want the Y to appear, we want to check the
namen in A2 against Sheet2 columnA. Let's set the range on sheet2 to 200
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 Sheet1


Christie;174798 Wrote:
I am trying to cross reference two worksheet and if a name appears on
both
worksheets a Y would appear in the box.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48360

Does anyone know how to cross reference two worksheets
 

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
---
"Christie" wrote:
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?

Does anyone know how to cross reference two worksheets
 

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

"Max" wrote:

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
---
"Christie" wrote:
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?

Does anyone know how to cross reference two worksheets
 

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(I SNUMBER(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
---
"Christie" wrote:
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???

Does anyone know how to cross reference two worksheets
 

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
---

Does anyone know how to cross reference two worksheets
 

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.

"Max" wrote:

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
---

Does anyone know how to cross reference two worksheets
 

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
---
"Christie" wrote in message
...
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.

Does anyone know how to cross reference two worksheets
 

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

"Max" wrote:

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
---
"Christie" wrote in message
...
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.

Does anyone know how to cross reference two worksheets
 

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/2...zy-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
---
"Christie" wrote in message
...
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

Does anyone know how to cross reference two worksheets
 

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

"Max" wrote:

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/2...zy-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
---
"Christie" wrote in message
...
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

Does anyone know how to cross reference two worksheets
 

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
---
"Christie" wrote in message
...
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

Does anyone know how to cross reference two worksheets
 

Thanks Max, will do :)

"Max" wrote:

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
---
"Christie" wrote in message
...
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