Formula for Matching Names and Conditional Formatting!
Glad to hear you got it working.
"Dan the Man" wrote:
Got it! Your great JMB. My office manager will love you!
Dan
"JMB" wrote:
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" wrote:
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
"Dan the Man" wrote:
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
|