| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compare two cells in two different files and return answer
Your ranges need to be the same length.
259<4707 -- 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
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
| Compare two cells and return certain value in third cell | Excel Worksheet Functions | |||
| Compare 2 columns and choose one and enter answer in third column | Excel Worksheet Functions | |||
| Is there a way to compare two excel files for repetitious cells? | Excel Worksheet Functions | |||
| Return answer if..... | Excel Worksheet Functions | |||
Linear Mode
