Is there a way to highlight duplicates in two columns?

[JenniferMurphy]

The built-in highlighting function for duplicates is very handy. But I can only get it to work on one table column. Is there a way to get it to treat 2 columns as if they were one?

In the tables below, it worked on the state columns. For the table on the left, I selected both name columns then added the highlighting. But it applies to each column separately. For the table on the right, I manually highlighted the cells that are duplicates in both name cells. That is how I would like it to work in the table the left. Is there any way to make that happen?

Conditional Highlighting.xlsx
	C	D	E	F	G	H	I
5	State	First Name	Last Name		State	First Name	Last Name
6	California	Ansel	Adams		California	Ansel	Adams
7	Utah	Butch	Cassidy		Utah	Butch	Cassidy
8	Vermont	Bernie	Sanders		Vermont	Bernie	Sanders
9	Maine	Stephen	King		Maine	Stephen	King
10	Chicago	Michael	Jordan		Chicago	Michael	Jordan
11	Alabama	Lionel	Richie		Alabama	Lionel	Richie
12	California	Joe	DiMaggio		California	Joe	DiMaggio
13	South Dakota	Kristi	Noem		South Dakota	Kristi	Noem
14	Nevada	Mark	Twain		Nevada	Mark	Twain
15	Washington, D. C.	Stephen	Colbert		Washington, D. C.	Stephen	Colbert
16	Delaware	Joe	Biden		Delaware	Joe	Biden
17	New York	Joe	DiMaggio		New York	Joe	DiMaggio
18	California	Arnold	Schwarzenegger		California	Arnold	Schwarzenegger
19	Massachusets	John	Adams		Massachusets	John	Adams
20	Tennessee	Michael	Jordan		Tennessee	Michael	Jordan
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H17:I17	Cell Value	duplicates	text	NO
H12:I12	Cell Value	duplicates	text	NO
H20:I20	Cell Value	duplicates	text	NO
H10:I10	Cell Value	duplicates	text	NO
G20	Cell Value	duplicates	text	NO
G10	Cell Value	duplicates	text	NO
C6:C20	Cell Value	duplicates	text	NO
D6:E20	Cell Value	duplicates	text	NO
G6:G9,G11:G19	Cell Value	duplicates	text	NO

 

[Eric W]

You can, but you need a custom rule:

Book1
	C	D	E
5	State	First Name	Last Name
6	California	Ansel	Adams
7	Utah	Butch	Cassidy
8	Vermont	Bernie	Sanders
9	Maine	Stephen	King
10	Chicago	Michael	Jordan
11	Alabama	Lionel	Richie
12	California	Joe	DiMaggio
13	South Dakota	Kristi	Noem
14	Nevada	Mark	Twain
15	Washington, D. C.	Stephen	Colbert
16	Delaware	Joe	Biden
17	New York	Joe	DiMaggio
18	California	Arnold	Schwarzenegger
19	Massachusets	John	Adams
20	Tennessee	Michael	Jordan
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:E20	Expression	=COUNTIFS($D$6:$D$20,$D6,$E$6:$E$20,$E6)>1	text	NO
C6:C20	Cell Value	duplicates	text	NO

 

[JenniferMurphy]

You can, but you need a custom rule:

Wow! It works. Thanks.

I guess I should have played with it a bit. I did ask the Internet, but got a bunch of irrelevant answers.

 

[JenniferMurphy]

I was concerned that the absolute expression you provided might not expand if rows are added to the table. I tested it and it seems to work. Is that guaranteed?

I tried an expression using table notation.

=COUNTIFS(Table13[First Name],Table13[@[First Name]],Table13[Last Name],Table13[@[Last Name]])>1

It doesn't work. Why is that?

I also noticed that Excel does not prompt with the CountIFs parameters like it does if I type the formula in a cell. That's unfortunate. I guess that was just too much trouble.

 

[Eric W]

I noodled around a bit with this, and came up with the same results that you did. Using standard formula syntax works fine on a table, and it expands with the table. I think you can count on that. That's intentional behavior. I actually found it annoying in cases where I didn't want it to expand.

As far as using table notation, I found the same thing too, that it doesn't work in CF. The "why" I'd have to speculate on. One reason is that the @ character for "current row" wouldn't have a table/position to relate to. But even when I wrote a formula that didn't use that, it still didn't work in CF. I'd guess that either they disallowed all table syntax in CF because of the @ issue, or because both CF and tables have a way to expand, and they didn't want to worry about those systems conflicting.

 

[JenniferMurphy]

Good points and thanks for the noodling.

This whole problem could have been avoided if M$FT could bother to do things right. If I select multiple columns and then do Conditional Formatting | Highlight Cells Rules | Duplicate Values ..., it could simply ask me if I want the columns treated separately or as a group. The chances of M$FT actually taking their users seriously are less than of winning the lottery. (sigh)