Conditional format issue

  • Thread starter Thread starter Patrick C. Simonds
  • Start date Start date
P

Patrick C. Simonds

I have a worksheet with 15 columns. Column 1 is labeled "Last Name" and
column 2 is labeled "First Name". I need to create a conditional format that
will turn a cell red if a name (first and last) appears more than two times.

I can create a conditional format that will work with the Last Name:

=IF(COUNTIF($A$7:$A$1000,$A140))>2

And one that works with the First Name:

=IF(COUNTIF($B$1:$B$1000,$B140))>2

But I can not think of a way to put them together in some way that works. I
tried:

=IF(COUNTIF($A$1:$A$1000,$A140),COUNTIF($B$1:$B$1000,$B140))>2

The problem I run into is that while last names are less like to be
associated to more than 1 person (although it certainly can me, which is why
I did not want to rely on last name alone) first names can be very common
among numerous people. So any ideas how I can count say the Bob Millers in
the list while excluding the Bob Adams, Bob Hills ect.?
 
Hi,

With a helper column.
Say your names are in columns A & B then in another column put the formula
=A1$B1
Drag down as required
Select column A and apply the conditional format formula and a colour
=COUNTIF($C$1:$C$20,C1)>1
Select coulmn B and apply the same conditional formula and a colour
Hide the helper column which in my case is Column C

Mike
 
Do column A and column B separately. Select, say, column A first. Put this
formula...

=AND(((COUNTIF($A$1:A1,A1)>1)=TRUE),((COUNTIF($B$1:B1,B1)>1)=TRUE))

in its formula field (and select red for its condition). Now select column B
and put the **same** formula in its formula field (and select red for its
condition).

Rick
 
I'm confused with the syntax in this SUMPRODUCT formula.
What do we expect ($A$7:$A$1000,$A7) or ($B$7:$B$1000,$B7) to produce?
 
Two things.... first, the two >1 conditions should be >2 as I just noticed
you said "appears more than two times" in your original message. However, we
can clean that formula up considerably. Just use this formula in each
column's formula field instead of the one I gave you originally...

=(COUNTIF($A$1:A1,A1)>2)*(COUNTIF($B$1:B1,B1)>2)

Rick
 
I just tried your formula in the Conditional Format's formula field and it
popped us this message... "You may not use unions, intersections, or array
constants for Conditional Formatting criteria".

Rick
 
Thank you, that worked sort of.

First I changed the formula a bit because I had to change the structure of
the worksheet, and I need to set the color yellow if there are two
occurrences of the name and red if there are more than two occurrences:

=AND(((COUNTIF($C$1:$C$1000,$C140)=2)=TRUE),((COUNTIF($D$1:D1000,$D140)=2)=TRUE))The problem is if there are only 2 Ross Charles to works fine but with inthe range (C1:C1000 and D1:D1000) if there is say a Miller Charles it nolonger works.As a conditional format I want it to look at the combined Last and Firstnames and then apply the format based on how many times that combinationexists."Rick Rothstein (MVP - VB)" <[email protected]> wrote inmessage Do column A and column B separately. Select, say, column A first. Put thisformula...>> =AND(((COUNTIF($A$1:A1,A1)>1)=TRUE),((COUNTIF($B$1:B1,B1)>1)=TRUE))>> in its formula field (and select red for its condition). Now select columnB and put the **same** formula in its formula field (and select red for itscondition).>> Rick>>> "Patrick C. Simonds" <[email protected]> wrote in messagehave a worksheet with 15 columns. Column 1 is labeled "Last Name" andcolumn 2 is labeled "First Name". I need to create a conditional format thatwill turn a cell red if a name (first and last) appears more than two times.>>>> I can create a conditional format that will work with the Last Name:>>>> =IF(COUNTIF($A$7:$A$1000,$A140))>2>>>> And one that works with the First Name:>>>> =IF(COUNTIF($B$1:$B$1000,$B140))>2>>>> But I can not think of a way to put them together in some way that works.I tried:>>>> =IF(COUNTIF($A$1:$A$1000,$A140),COUNTIF($B$1:$B$1000,$B140))>2>>>> The problem I run into is that while last names are less like to beassociated to more than 1 person (although it certainly can me, which is whyI did not want to rely on last name alone) first names can be very commonamong numerous people. So any ideas how I can count say the Bob Millers inthe list while excluding the Bob Adams, Bob Hills ect.?>
 
You will need two Conditional Format conditions for each column to do that
(click the Add button to create space for the 2nd condition). Select one of
the two columns and put this formula in Condition 1....

=(COUNTIF($A$1:A1,A1)>2)*(COUNTIF($B$1:B1,B1)>2)

and set its format color to red. Now put this formula in Condition 2...

=(COUNTIF($A$1:A1,A1)>1)*(COUNTIF($B$1:B1,B1)>1)

and set its format color to yellow. Now select the other column and do
exactly the same thing.

Rick
 
Back
Top