Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross Reference Formula | Excel Worksheet Functions | |||
Cross reference | Excel Discussion (Misc queries) | |||
cross reference two | New Users to Excel | |||
Cross reference | Excel Worksheet Functions | |||
Cross Reference | Excel Worksheet Functions |