Formula for Matching Names and Conditional Formatting!

[Dan the Man]

My Office Manager has a spreadhsheet containing last names, first names,
addresses, etc. As the spreadsheet gets longer, she worries about accidently
entering identical information (last and first names) without realizing it.
What I'd like to do is the following:

Put a Flag in Cell A1 which says: "Matches Found", and use conditional
formatting to turn any of the matches "red" (last name and first name) for
easy identification. If no matches occur, the flag in Cell A1 would say: "No
Matches Found".

If an identical match occurs in Column A or B

Jones, Paul B.
Doe, Leo
Jones, Paul B.

Of course I'd like the formula to be clear enough so a match is not
triggered when similar but not EXACT matches occur:

Jones, Paul B.
Jones, Mary C.
Doe, Leo T.
Smith, Frank L.
Smith, Mary

Any suggestions? I know she will be excited if I can give her this
information!

Dan

 

[JMB]

Assuming the names data is in B1:C4, try this in cell A1 (array entered
w/Cntrl+Shift+Enter). It should ignore instances where both column B and C
are empty or blank.

=IF(SUM(IF(B1:B4&C1:C4<>"",--(MATCH(B1:B4&C1:C4,B1:B4&C1:C4,0)=ROW(B1:B4)-MIN(ROW(B1:B4))+1),0))=SUM(--((B1:B4<>"")+(C1:C4<>"")>0)),
"No Dupes", "Duplicates")

for the conditional formatting, select your range, conditional formatting,
formula is:
=SUMPRODUCT(--($B$1:$B1=$B1),--($C$1:$C1=$C1))>1
which will flag only the subsequent occurences. To flag all occurences use:
=SUMPRODUCT(--($B$1:$B$4=$B1),--($C$1:$C$4=$C1))>1


The real issue will be whether or not the data is entered consistently.
There may be extra spaces (you could incorporate TRIM into the above
functions to correct) or perhaps a missing period, or the middle initial is
not keyed. For example:

Jones Paul J
Jones Paul
Jones Paul J.

will all be seen as unique by the formula. Would be better if you have a
customer#, SSN#, or some other unique identifier (and, of course, the same
name doesn't always mean it is the same person).

 

[Gord Dibben]

Go to Chip Pearson's Topic Index.

http://www.cpearson.com/excel/topic.htm


Scroll down to the "D" section and see the Duplicates links.

Chip shows methods on highlighting, preventing, replacing.



Gord Dibben MS Excel MVP

 

[Dan the Man]

Thanks JMB, the matching flags worked perfectly. I understand your comments
regarding unique identifiers, and I shall mention that to our office manager.
Very good thought. I did move the formula around a bit to meet the needs of
her spreadsheet (and upon testing it works fine):

=IF(SUM(IF(A4:A3500&B4:B3500<>"",--(MATCH(A4:A3500&B4:B3500,A4:A3500&B4:B3500,0)=ROW(A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<>"")+(B4:B3500<>"")>0)),
"No Duplicates", "Duplicates")

I couldn't seem to get the conditional formatting to work exactly as I'd
like it however, so if you have an additional suggestion for that. My goal is
the cells turn a specifc color (in Columns A & B-last and first name) when a
"Match" occurs. My preference would be that ALL matches change color when the
match takes place.

Smith, Paul L.
Doe, Leo C.
Jones, Mary R.
Smith, Paul L.
Doe, Leo C.

All the above exact matches in Column A and B should change color (both
instances of Paul and both instances of Leo).

Thanks again,

Dan

 

[JMB]

You should be able to select A4:B3500 and click Conditional Formatting,
Formula is:

=SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))>1

and then select your format.

 

[Dan the Man]

Got it! Your great JMB. My office manager will love you!

Dan

 

[JMB]

Glad to hear you got it working.

Got it! Your great JMB. My office manager will love you!

Dan