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$10 00,$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


"Patrick C. Simonds" wrote:

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$10 00,$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.?

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


"Patrick C. Simonds" wrote in message
...
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$10 00,$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.?

=SUMPRODUCT(($A$7:$A$1000,$A7)*($B$7:$B$1000,$B7)) 2


"Patrick C. Simonds" wrote:

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$10 00,$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.?

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?
--
David Biddulph

"Teethless mama" wrote in message
...
=SUMPRODUCT(($A$7:$A$1000,$A7)*($B$7:$B$1000,$B7)) 2


"Patrick C. Simonds" wrote:

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$10 00,$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.?

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Patrick C. Simonds" wrote in message
...
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$10 00,$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.?

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


"Teethless mama" wrote in message
...
=SUMPRODUCT(($A$7:$A$1000,$A7)*($B$7:$B$1000,$B7)) 2


"Patrick C. Simonds" wrote:

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$10 00,$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.?

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),((COUN TIF($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)" 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" wrote in . ..I have 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$10 00,$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


"Patrick C. Simonds" wrote in message
...
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),((COUN TIF($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)"
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"
wrote in . ..I have 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$10 00,$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.?