conditional formatting for duplicates

I am trying to conditionally format a list of approximately 12,000 addresses so that entries that are duplicates in two categories are highlighted. I tried using the formula

=AND(COUNTIF($B$2:$B$11,B2)>1,COUNTIF($C$2:$C$11,C2)>1)

The problem with this algorithm is that it returns entries that match others
in both areas regardless of whether the matches are in the same entry. For
example if I have the list:

John Cincinnati
Zach New York
Zach Cincinnati

The last entry will be highlighted although it is not a true duplicate.

In addition, it slows my spreadsheet to a crawl, even when I save and reopen it takes five minutes to load.

Finally, I am unable to edit the format of individual entries, which is necessary for when I go through to check for errors.

Any help would be appreciated.

Thanks,
Zach

There's probably a clever formula which will do it in one go, but otherwise a workaround would be to add a third column which concatenates the other two and do the CF on that.

Hi, Zach. Try using Sumproduct:

=SUMPRODUCT(($B$2:$B$11=B2)*($C$2:$C$11=C2))>1

HTH

Jason

SUMPRODUCT does nothing when I try it, but I can't believe I didn't think of concatenating the columns.

However, being new to Excel, here come the dumb questions. How do I go about concatenating two entire columns into a third?

Also, any ideas why I can't edit the individual cell formats after conditional formatting? That would be a big help.

Thanks,
Zach

jason's formula should work and doesn't require anything else - have you adapted his formula in some way?

If you do wish to concatenate then just in D2
=B2&C2
and add your CF there
=COUNTIF($D$2:$D$11,D2)>1

Hi, Zach.

If you highlight the range B2:C11, starting with cell B2 (now B2 is the active cell), enter this formula, and it should work:

=SUMPRODUCT(($B$2:$B$11=B$2)*($C$2:$C$11=C$2))>1

On my formula above, it should have worked if you entered it into only column B. Adding in the extra 2 $'s now allows it to be entered into both columns B and C at the same time. This is the solution if you don't want the extra unnecessary column.

HTH

Jason

Big thanks to everyone who helped! I ended up just concatenating to a third column, but I'm sure with some more tinkering I could get the SUMPRODUCT to work. Totally saved my workday.

-Zach