Conditional format issue
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.?
|