Compare two cells in two different files and return answer

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

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.
 
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
 
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
 
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 said:
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 said:
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.
Click to expand...
.
Click to expand...
 
I'm using the 2003 version?


Bernard Liengme said:
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 said:
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.
Click to expand...
.
Click to expand...
 
Your ranges need to be the same length.
259<>4707
--
David Biddulph

Doug said:
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 said:
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 said:
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.
Click to expand...
.
Click to expand...
Click to expand...
 
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
 
Back
Top