Highlight duplicate entries matching 2 criteria in another workshe

R

RS

In Excel 2000, I’m told that I can’t reference other worksheets or workbooks
when using conditional formatting. Looking through these forums, I put
together a formula that highlights cells if there are duplicate listings of
companies found in column S (row 2 has headers and the companies are listed
in cells S3:S261. The conditional format in this “Rates.xls†file for S3,
for example, is:
Formula Is =COUNTIF(S:S,S3)>1.

I want to use a similar conditional format to highlight cells in column G of
my other workbook (Test Rate) whereby I find pay rates from the “Rates†file
based on TWO criteria: column E (company name) that matches values in column
S of the “Rates†file; and column F, which concatenates columns B (Type), C
(Program), & D (Model) with /’s in the “Test Rate†file to match the values
found in column K of the “Rates†file. Since I’m told that I can’t reference
other worksheets or workbooks in the conditional format, I’ve created a
formula in column L which basically checks to see if BOTH conditions are met
and returns either True or False. The formula in cell L8 is:
=AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)>1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)>1)

My problem is that this formula is evaluating each condition independently
and I need it to evaluate BOTH conditions TOGETHER. Currently this formula
is incorrectly returning TRUE for an entry that should be FALSE because
although there is >1 company listing in the “Rates†file (this evaluates as
TRUE), AND there is >1 listing of the value in F8 (Networks/Res/Home; which
also evaluates as TRUE), the combination of both items together results in
only 1 value for each combination.

In other words, even though there are 2 listings for “Company Aâ€, the values
in column F are different for each of the 2 listings. Column F of one of
Company A’s listings is Networks/Res/School (of which there are 58 listings)
and in the other of Company A’s listings, column F is Networks/Res/Home (of
which there are 70 listings). Because the formula is currently evaluating
each condition independently, the 2nd half of the formula is returning TRUE
because there are multiple listings for this half, but evaluating BOTH
conditions TOGETHER, the formula should return FALSE. How do I correct my
formula?
 
R

RS

Hi Gary's Student,

Since I've never used SUMPRODUCT before, I looked at the Excel Help (no
help there) and the forums here to try and fix my equation. What I came up
with is the following:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)>1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)>1)

However, I'm doing something wrong, because it's returning 0 for something
that, with the other formula, gave a result of TRUE because there was >1
possible value for the 2 matching criteria. What am I doing wrong?

Gary''s Student said:
use SUMPRODUCT()
--
Gary''s Student
gsnu200709


RS said:
In Excel 2000, I’m told that I can’t reference other worksheets or workbooks
when using conditional formatting. Looking through these forums, I put
together a formula that highlights cells if there are duplicate listings of
companies found in column S (row 2 has headers and the companies are listed
in cells S3:S261. The conditional format in this “Rates.xls†file for S3,
for example, is:
Formula Is =COUNTIF(S:S,S3)>1.

I want to use a similar conditional format to highlight cells in column G of
my other workbook (Test Rate) whereby I find pay rates from the “Rates†file
based on TWO criteria: column E (company name) that matches values in column
S of the “Rates†file; and column F, which concatenates columns B (Type), C
(Program), & D (Model) with /’s in the “Test Rate†file to match the values
found in column K of the “Rates†file. Since I’m told that I can’t reference
other worksheets or workbooks in the conditional format, I’ve created a
formula in column L which basically checks to see if BOTH conditions are met
and returns either True or False. The formula in cell L8 is:
=AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)>1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)>1)

My problem is that this formula is evaluating each condition independently
and I need it to evaluate BOTH conditions TOGETHER. Currently this formula
is incorrectly returning TRUE for an entry that should be FALSE because
although there is >1 company listing in the “Rates†file (this evaluates as
TRUE), AND there is >1 listing of the value in F8 (Networks/Res/Home; which
also evaluates as TRUE), the combination of both items together results in
only 1 value for each combination.

In other words, even though there are 2 listings for “Company Aâ€, the values
in column F are different for each of the 2 listings. Column F of one of
Company A’s listings is Networks/Res/School (of which there are 58 listings)
and in the other of Company A’s listings, column F is Networks/Res/Home (of
which there are 70 listings). Because the formula is currently evaluating
each condition independently, the 2nd half of the formula is returning TRUE
because there are multiple listings for this half, but evaluating BOTH
conditions TOGETHER, the formula should return FALSE. How do I correct my
formula?
Click to expand...
Click to expand...
 
G

Gary''s Student

You may need to enclose the >1 within the --

In any case, debug the SUMPRODUCT before using it for the formatting. Help
is not very helpful, instead see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary's Student
gsnu200709


RS said:
Hi Gary's Student,

Since I've never used SUMPRODUCT before, I looked at the Excel Help (no
help there) and the forums here to try and fix my equation. What I came up
with is the following:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)>1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)>1)

However, I'm doing something wrong, because it's returning 0 for something
that, with the other formula, gave a result of TRUE because there was >1
possible value for the 2 matching criteria. What am I doing wrong?

Gary''s Student said:
use SUMPRODUCT()
--
Gary''s Student
gsnu200709


RS said:
In Excel 2000, I’m told that I can’t reference other worksheets or workbooks
when using conditional formatting. Looking through these forums, I put
together a formula that highlights cells if there are duplicate listings of
companies found in column S (row 2 has headers and the companies are listed
in cells S3:S261. The conditional format in this “Rates.xls†file for S3,
for example, is:
Formula Is =COUNTIF(S:S,S3)>1.

I want to use a similar conditional format to highlight cells in column G of
my other workbook (Test Rate) whereby I find pay rates from the “Rates†file
based on TWO criteria: column E (company name) that matches values in column
S of the “Rates†file; and column F, which concatenates columns B (Type), C
(Program), & D (Model) with /’s in the “Test Rate†file to match the values
found in column K of the “Rates†file. Since I’m told that I can’t reference
other worksheets or workbooks in the conditional format, I’ve created a
formula in column L which basically checks to see if BOTH conditions are met
and returns either True or False. The formula in cell L8 is:
=AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)>1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)>1)

My problem is that this formula is evaluating each condition independently
and I need it to evaluate BOTH conditions TOGETHER. Currently this formula
is incorrectly returning TRUE for an entry that should be FALSE because
although there is >1 company listing in the “Rates†file (this evaluates as
TRUE), AND there is >1 listing of the value in F8 (Networks/Res/Home; which
also evaluates as TRUE), the combination of both items together results in
only 1 value for each combination.

In other words, even though there are 2 listings for “Company Aâ€, the values
in column F are different for each of the 2 listings. Column F of one of
Company A’s listings is Networks/Res/School (of which there are 58 listings)
and in the other of Company A’s listings, column F is Networks/Res/Home (of
which there are 70 listings). Because the formula is currently evaluating
each condition independently, the 2nd half of the formula is returning TRUE
because there are multiple listings for this half, but evaluating BOTH
conditions TOGETHER, the formula should return FALSE. How do I correct my
formula?
Click to expand...
Click to expand...
Click to expand...
 
M

Max

=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)>1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)>1)

Perhaps try it as
=SUMPRODUCT(([Rates.xls]Sheet1!$S$3:$S$261=$E8)*([Rates.xls]Sheet1!$K$3:$K$261=$F8))
 
R

RS

Dear Gary’s Student,

Sorry for the delay in getting back to you, but this is the first I chance I
had to see the new replies to my post. I tried your suggestion and came up
with the following change to my equation:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E9>1),--('[Rates.xls]Sheet1'!$K$3:$K$261=$F9>1))

However, this simply counts all occurrences of all the companies in the
Rates table. I tried Max’s solution below and that correctly displays the
number of matching results found for the multiple criteria which are being
evaluated. I took a look at the link that you provided, and while I haven’t
finished reading the whole page yet, it’s helping me understand the
SUMPRODUCT function. One thing I learned was that by using the SUMPRODUCT
function, I don’t need to keep my Rates workbook open to find the results.
Thanks for your help and look forward to reading the rest of the link that
you gave me.

Gary''s Student said:
You may need to enclose the >1 within the --

In any case, debug the SUMPRODUCT before using it for the formatting. Help
is not very helpful, instead see:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary's Student
gsnu200709


RS said:
Hi Gary's Student,

Since I've never used SUMPRODUCT before, I looked at the Excel Help (no
help there) and the forums here to try and fix my equation. What I came up
with is the following:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)>1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)>1)

However, I'm doing something wrong, because it's returning 0 for something
that, with the other formula, gave a result of TRUE because there was >1
possible value for the 2 matching criteria. What am I doing wrong?

Gary''s Student said:
use SUMPRODUCT()
--
Gary''s Student
gsnu200709


:

In Excel 2000, I’m told that I can’t reference other worksheets or workbooks
when using conditional formatting. Looking through these forums, I put
together a formula that highlights cells if there are duplicate listings of
companies found in column S (row 2 has headers and the companies are listed
in cells S3:S261. The conditional format in this “Rates.xls†file for S3,
for example, is:
Formula Is =COUNTIF(S:S,S3)>1.

I want to use a similar conditional format to highlight cells in column G of
my other workbook (Test Rate) whereby I find pay rates from the “Rates†file
based on TWO criteria: column E (company name) that matches values in column
S of the “Rates†file; and column F, which concatenates columns B (Type), C
(Program), & D (Model) with /’s in the “Test Rate†file to match the values
found in column K of the “Rates†file. Since I’m told that I can’t reference
other worksheets or workbooks in the conditional format, I’ve created a
formula in column L which basically checks to see if BOTH conditions are met
and returns either True or False. The formula in cell L8 is:
=AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)>1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)>1)

My problem is that this formula is evaluating each condition independently
and I need it to evaluate BOTH conditions TOGETHER. Currently this formula
is incorrectly returning TRUE for an entry that should be FALSE because
although there is >1 company listing in the “Rates†file (this evaluates as
TRUE), AND there is >1 listing of the value in F8 (Networks/Res/Home; which
also evaluates as TRUE), the combination of both items together results in
only 1 value for each combination.

In other words, even though there are 2 listings for “Company Aâ€, the values
in column F are different for each of the 2 listings. Column F of one of
Company A’s listings is Networks/Res/School (of which there are 58 listings)
and in the other of Company A’s listings, column F is Networks/Res/Home (of
which there are 70 listings). Because the formula is currently evaluating
each condition independently, the 2nd half of the formula is returning TRUE
because there are multiple listings for this half, but evaluating BOTH
conditions TOGETHER, the formula should return FALSE. How do I correct my
formula?
Click to expand...
Click to expand...
Click to expand...
 
R

RS

Dear Max,

Sorry for the delay in getting back to you, but this is the first I chance I
had to see the new replies to my post. I tried what you suggested but Excel
told me that the name wasn’t defined. I noticed that there were no single
quotes referring to the location of my lookup table in your equation, so I
added them back in and then the formula correctly displayed the number of
results matching the criteria given. The correct formula is:
=SUMPRODUCT(('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=$F8))

I then simply added a conditional format that says if the text is >1,
highlight the cell. What I like about SUMPRODUCT is that I can keep my Rates
file closed and it will still evaluate the equation. Thank you for your
help. I also took a look at the link of some of the solutions you have given
to other people and found them to be very interesting as well.

Using this same concept of calculating from a closed workbook, would it be
possible to modify my current formula for displaying the rates from this
Rates file while it is closed. Currently, my formula requires me to keep the
Rates file open when looking-up the rates. Here is the array-entered formula:
=INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0))

where from the Rates.xls file, column O contains the pay rates to be found
and S contains the Company names to be matched against based on values in my
test sheet. From my test sheet, row 1 has headers in it. Column B is
“Typeâ€, column C “Programâ€, column D “Modelâ€, column E “Companyâ€, and column
G is where I want the rates to appear. In the formula, columns, B, C, & D
are concatenated with /’s to match the values found in column K of the Rates
sheet (Example of column K: Networks/Res/Home; following the format
$B8/$C8/$D8).

The bottom half of this post was actually part of another question I had
originally posted back on March 1. After 2 posters didn’t provide a
solution, I tried reposting the question 5 days later. No one replied to
that post either. If you could take a look at that original post that would
be greatly appreciated. Here is the link:
http://www.microsoft.com/office/com...&p=1&tid=411bb737-3550-4828-9e9a-0f3db8c89393

Thanks again for your help.

Max said:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)>1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)>1)

Perhaps try it as:
=SUMPRODUCT(([Rates.xls]Sheet1!$S$3:$S$261=$E8)*([Rates.xls]Sheet1!$K$3:$K$261=$F8))

RS said:
...Since I've never used SUMPRODUCT before, I looked at the Excel Help (no
help there) and the forums here to try and fix my equation. What I came up
with is the following:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)>1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)>1)

However, I'm doing something wrong, because it's returning 0 for something
that, with the other formula, gave a result of TRUE because there was >1
possible value for the 2 matching criteria. What am I doing wrong?
Click to expand...
Click to expand...
 
M

Max

From my tests here, your array INDEX/MATCH seems to be able to update ok
from the closed Rates file.

But if you want to try the equiv. SP version,
here it is, right out of the formula cell (with rates.xls** open):
=SUMPRODUCT(([rates.xls]Sheet1!$S$3:$S$261=$E8)*([rates.xls]Sheet1!$K$3:$K$261=$B8&"/"&$C8&"/"&$D8),[rates.xls]Sheet1!$O$3:$O$261)
(simplified the CONCAT bit of it)

**my test dependent file was named: rates.xls

For the SP equivalent to work, it assumes col O in rates.xls contain numbers
(not text), and that there's only one unique match within the criteria
array. If there's more than 1 match within the array, then it'll return the
sum of all the payrates in col A corresponding to the matches which of
course is a wrong return here.

RS said:
Dear Max,

Sorry for the delay in getting back to you, but this is the first I chance
I
had to see the new replies to my post. I tried what you suggested but
Excel
told me that the name wasn't defined. I noticed that there were no single
quotes referring to the location of my lookup table in your equation, so I
added them back in and then the formula correctly displayed the number of
results matching the criteria given. The correct formula is:
=SUMPRODUCT(('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=$F8))

I then simply added a conditional format that says if the text is >1,
highlight the cell. What I like about SUMPRODUCT is that I can keep my
Rates
file closed and it will still evaluate the equation. Thank you for your
help. I also took a look at the link of some of the solutions you have
given
to other people and found them to be very interesting as well.

Using this same concept of calculating from a closed workbook, would it be
possible to modify my current formula for displaying the rates from this
Rates file while it is closed. Currently, my formula requires me to keep
the
Rates file open when looking-up the rates. Here is the array-entered
formula:
=INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0))

where from the Rates.xls file, column O contains the pay rates to be found
and S contains the Company names to be matched against based on values in
my
test sheet. From my test sheet, row 1 has headers in it. Column B is
"Type", column C "Program", column D "Model", column E "Company", and
column
G is where I want the rates to appear. In the formula, columns, B, C, & D
are concatenated with /'s to match the values found in column K of the
Rates
sheet (Example of column K: Networks/Res/Home; following the format
$B8/$C8/$D8).

The bottom half of this post was actually part of another question I had
originally posted back on March 1. After 2 posters didn't provide a
solution, I tried reposting the question 5 days later. No one replied to
that post either. If you could take a look at that original post that
would
be greatly appreciated. Here is the link:
http://www.microsoft.com/office/com...&p=1&tid=411bb737-3550-4828-9e9a-0f3db8c89393

Thanks again for your help.
Click to expand...
 
R

RS

Dear Max,

You’re right about the INDEX/MATCH working with a closed workbook. I
have been learning so many new functions trying to solve my multiple pay rate
problem that I mixed up INDEX/MATCH with the COUNTIF fx (which doesn’t work
with a closed workbook), which is what someone originally suggested I use
before you provided the correct SUMPRODUCT solution.

For the most part, the current INDEX/MATCH formula works fine, but 14
of the companies have more than 1 pay rate available. For these companies,
the formula only finds the first instance. Is there a way that I could have
a dependent drop down list of results for these companies without sorting the
source data located in the Rates file or using any filters. I can’t have
multiple results spread out over more than 1 cell (because this will result
in many listings with blank cells, thus changing the format of my sheet) nor
do I want to have the results concatenated into 1 long cell.

I know that Debra Dalgleish has described how to create dependent
drop-down lists on her site, but in those cases, it requires either having a
sorted list or defining names for each list. Since I have almost 260
companies in the Rates file with other data listed in other columns, I would
rather not sort this list or try and define names for all these companies.

Maybe the solution to this would be to have some sort of combination
of this INDEX/MATCH formula and vba or data validation that would provide a
list for these 14 companies. Maybe something that would say, use the
formula, but if any of these 14 companies with their multiple rates are
found, use a data-validated list or vba to display the results in a list.
Would this be possible?
 
M

Max

I'm out here, sorry. Perhaps you might want to try a post
in .programming for a vba solution. Good luck !
 
R

RS

Dear Max,

Thanks anyway for your help in fixing this aspect of my problem. Will
try your suggestion and post in the Programming forum.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MAX/MAXA – Formula not returning required value 6
Multiple criteria with multiple results in one cell 4
Multi-Condition Formula 5
Help with conditional formatting in Excel 2010 8
VLOOKUP over three spreadsheets 8
Vlookup and column reference problems 15
How can I convert many rows of hyperlinks to BASE links? 1
Help with a formmula change in a "flag" parameter! 19

Top