Highlighting duplicate first and last names

[bearcub]

I get lists from our offices where they have multiple records for one employee. I would like to highlight those cells where the first and last names appear more the once. In other words, highlight all the records that are duplicated using the first and last name fields (two different columns).

I normally have conditional formatting set for the member ID number but sometimes we don't get this different from the offices.

I tried using the Countifs function as follows but it is giving me incorrect results (I think I set it up incorrectly):

COUNTIFS($B:$B, $B2,$C:$C,$C2)>1

Where Column B has the last names and column C the first names. Column A is where I would have Member ID numbers but in this instance I wasn't provided with that information.

I was trying to google the formula but I didn't know exactly how to phrase it so I find the solution

Thank you for your help.

Michael

 

[bearcub]

I did find something like this on one of the Microsoft Boards the does work:

AND(LEN($A1&$B1),COUNTIFS($A$1:$A$9999,$A1,$B$1:$B$9999,$B1)>1)

There was a comment that this one tends use a lot of memory. Are the other formulas that isn't as much CPU intensive?

Looking at this formula I'm trying to understand how it is working. It seems there counting the characters in A & B (it is an array formula at this point, correct)? Then it is count a cell only if the A or B appears more than once. This part I understand but how does the Len function help identify the names in columns A and B?

Thank you for your help.

Michael

 

[Jeffrey Mahoney]

Your formula will return TRUE if it found more than one instance of that first and last name. If your formula is in column D, this is the conditional formatting formula you would use to highlight all the duplicates.
=$D2=TRUE

 

[bearcub]

thank you Jeffrey,

I actually have this as a conditional formatting rule and it works fine (post #2 ). I was just curious how the Len function works in that formula.

Michael

 

[Peter_SSs]

The formula from post 1 =COUNTIFS($B:$B, $B2,$C:$C,$C2)>1 should work straight off in CF if applied to the range B2:C??

As a general rule I would recommend avoiding whole column references where possible, so I would prefer something more like the following. Just make sure the maximum row number is greater than the number of rows you will ever have.

=COUNTIFS($B$2:$B$1000, $B2,$C$2:$C$1000,$C2)>1

Edit: You shouldn't need the len() part of that formula in post #2 . What happens if you just use =COUNTIFS($A$1:$A$9999,$A1,$B$1:$B$9999,$B1)>1 applied from row 1? (have the columns changed? You originally said this was in columns B:C)

 

[bearcub]

Thank you, Peter, good point. When I tried it the first time it didn't work but it did this time. I just added the countifs and, like you said, it works straight off.


It sounds like the Len function doesn't add any value, does it? Here is the link to article to which i was referring - it was from 2012 though I think it should still apply today.

https://answers.microsoft.com/en-us...-columns/71574b8e-19f0-41cb-b98e-f8aa1eaf044d

Thank you again for your help,

Michael

 

[Peter_SSs]

Here is the link to article to which i was referring - it was from 2012 though I think it should still apply today.

That was just another member in another forum & I think they just didn't account for the fact that COUNTIFS ignores blanks anyway.

 

[bearcub]

That's good to know. I thought I was missing something with that formula because I couldn't see where Len would help. So, you're saying that Len was used to count only the characters while ignoring the spaces in addition to countifs being programmed to ignore it anyway?

 

[Peter_SSs]

That's good to know. I thought I was missing something with that formula because I couldn't see where Len would help. So, you're saying that Len was used to count only the characters while ignoring the spaces in addition to countifs being programmed to ignore it anyway?

Some CF formulas will result in blank cells being highlighted when the user really only want highlights on cells with data that meet some condition. Using LEN() in conjunction with the existing CF formula is one way to stop blanks being highlighted.

For example, in columns B & D the CF is applied to rows 2:10.

Excel Workbook
	B	C	D
2	1		1
3	13		13
4	5		5
5	15		15
6
7
8
9
10
CF Example

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =B2<10	Abc
D2	1. / Formula is =AND(LEN(D2),D2<10)	Abc

 

[bearcub]

Ah, I see. How does using the Len function prevent the 0's from showing., I don't understand, sorry.