|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 16th, 2009, 02:55 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Compare two cells in two different files and return answer
I am attempting to compare two text cells and if they both exist, I want to
bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact match somewhere in file 2 then return a "yes" in column C. If not, "no" in column C. So, file 2 would look like this: File 2 Col A Col B Col C BRATTA ZPB_COMMON Yes AHERNC ZPB_COMMON Yes GENCABH No Thanks in advance for any help. 
|
|
#2
November 16th, 2009, 03:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare two cells in two different files and return answer
In C1 of Sheet2
=IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!$A$1:$A$20=A1),--('[My First File.xls]Sheet1'!$B$1:$B$20=B1)),"Yes","No") Replace the two '20' entries by however number of rows your need to compare Copy down the column Only in Excel 2007 can you use full column references as in =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!A:A=A1),--('[My First File.xls]Sheet1'!B:B=B1)),"Yes","No") best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Doug" wrote in message ... I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact match somewhere in file 2 then return a "yes" in column C. If not, "no" in column C. So, file 2 would look like this: File 2 Col A Col B Col C BRATTA ZPB_COMMON Yes AHERNC ZPB_COMMON Yes GENCABH No Thanks in advance for any help.
|
|
#3
November 16th, 2009, 03:16 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare two cells in two different files and return answer
If you are using Excel 2007
=IF(COUNTIFS('[My First File.xls]Sheet1'!$A:$A,A1,'[My First File.xls]Sheet1'!$B:$B,B1),"y","n") best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Doug" wrote in message ... I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact match somewhere in file 2 then return a "yes" in column C. If not, "no" in column C. So, file 2 would look like this: File 2 Col A Col B Col C BRATTA ZPB_COMMON Yes AHERNC ZPB_COMMON Yes GENCABH No Thanks in advance for any help.
|
|
#4
November 16th, 2009, 04:00 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare two cells in two different files and return answer
Thanks Bernard.
But, I'm still having an issue. I get a #value error for my return. Here is the formula as a wrote it. =IF(SUMPRODUCT(--($C$2:$I$259=C2),--('New Composites.xls'!$A$2:$B$4707='New Composites.xls'!$A$2)),"Yes","no") Any help is appreciated. Thanks again "Bernard Liengme" wrote: In C1 of Sheet2 =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!$A$1:$A$20=A1),--('[My First File.xls]Sheet1'!$B$1:$B$20=B1)),"Yes","No") Replace the two '20' entries by however number of rows your need to compare Copy down the column Only in Excel 2007 can you use full column references as in =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!A:A=A1),--('[My First File.xls]Sheet1'!B:B=B1)),"Yes","No") best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Doug" wrote in message ... I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact match somewhere in file 2 then return a "yes" in column C. If not, "no" in column C. So, file 2 would look like this: File 2 Col A Col B Col C BRATTA ZPB_COMMON Yes AHERNC ZPB_COMMON Yes GENCABH No Thanks in advance for any help. .
|
|
#5
November 16th, 2009, 04:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare two cells in two different files and return answer
I'm using the 2003 version?
"Bernard Liengme" wrote: If you are using Excel 2007 =IF(COUNTIFS('[My First File.xls]Sheet1'!$A:$A,A1,'[My First File.xls]Sheet1'!$B:$B,B1),"y","n") best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Doug" wrote in message ... I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact match somewhere in file 2 then return a "yes" in column C. If not, "no" in column C. So, file 2 would look like this: File 2 Col A Col B Col C BRATTA ZPB_COMMON Yes AHERNC ZPB_COMMON Yes GENCABH No Thanks in advance for any help. .
|
|
#6
November 16th, 2009, 04:17 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare two cells in two different files and return answer
Your ranges need to be the same length.
2594707 -- David Biddulph "Doug" wrote in message ... Thanks Bernard. But, I'm still having an issue. I get a #value error for my return. Here is the formula as a wrote it. =IF(SUMPRODUCT(--($C$2:$I$259=C2),--('New Composites.xls'!$A$2:$B$4707='New Composites.xls'!$A$2)),"Yes","no") Any help is appreciated. Thanks again "Bernard Liengme" wrote: In C1 of Sheet2 =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!$A$1:$A$20=A1),--('[My First File.xls]Sheet1'!$B$1:$B$20=B1)),"Yes","No") Replace the two '20' entries by however number of rows your need to compare Copy down the column Only in Excel 2007 can you use full column references as in =IF(SUMPRODUCT(--('[My First File.xls]Sheet1'!A:A=A1),--('[My First File.xls]Sheet1'!B:B=B1)),"Yes","No") best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Doug" wrote in message ... I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact match somewhere in file 2 then return a "yes" in column C. If not, "no" in column C. So, file 2 would look like this: File 2 Col A Col B Col C BRATTA ZPB_COMMON Yes AHERNC ZPB_COMMON Yes GENCABH No Thanks in advance for any help. .
|
|
#7
November 16th, 2009, 04:53 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare two cells in two different files and return answer
Hi
Try this =IF(SUMPRODUCT(--([Book1]Sheet1!$A$2:$A$4=A2),--([Book1]Sheet1!$B$2:$B$4=B2)),"Yes","No") Bear in mind that your range need to be the same,eg the range A2 to A4 in Sheet1 of Book1 must be the same in Sheet1 of Book2, ie A2 to A4, otherwise you will get error message -- Hope this help Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Doug" wrote: I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact match somewhere in file 2 then return a "yes" in column C. If not, "no" in column C. So, file 2 would look like this: File 2 Col A Col B Col C BRATTA ZPB_COMMON Yes AHERNC ZPB_COMMON Yes GENCABH No Thanks in advance for any help.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
