Hi Toppers!

I wasn't sure how to weave your formula into the one I have below. As a
stand alone I couldn't get yours to work. I'll assume I need to weave it into
mine to get it to work. By the way, the "x" mark will be placed in Column C.
You did accurately reflect how I will use those "x" marks (placing them in
Column C next to all occurences of the same name (I have the spreadhseet
listed as last name, first name versus first name, last name). Thanks for any
further suggestion.

Best,

Dan

"Toppers" wrote:

=IF(SUMPRODUCT(--($A$4:$A$3500=$A5),--($B$4:$B$3500=$B5))1,"x","")

will place an "x" in any row where duplicate names occur.

1 Ian Black
2 John Smith x
3 John Smith x
4 Dave Brown

Is this what you want?

"Dan the Man" wrote:

I was given a wonderful formula to identify and duplicate name entries on a
spreadsheet, and it works like a charm:

=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 Duplicate Names Found", "Duplicate Names Found")

I also use "conditional formatting" (see below) to change all of the
applicable "matches" to RED when they occur

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

Here is my question:

Every now and then a duplicate entry is okay (as the duplicate identifies a
different location address for an individual, or a different service that we
are providing them). The main goal of the original formula above was to
identify "accidental" entering of the same persons data more than once.

My thought is to develop a third Row (Row C), and place an "x" in any cell
where the duplicate names occur, but shouldn't be flagged (for the reasons
described above as to why we sometimes wouldn't consider this a duplicate
entry).

What formula would best incorporate that additional requirement (to
effectively turn off the "Duplicate Names Found" flag, and return to font to
its original status). I suspect that once the "Duplicate Names Found" flag
returns to "No Duplicate Names Found", the conditional formatting formula
currently in place will take care of itself.

Thanks much,

Dan