Thanks Teethless Mama. That didn't seem to work, as it didn't account for
both matches (DUP) and non matches (NO DUP). I appreciate your input however.
I won't give up until I get this one.

Dan

"Teethless mama" wrote:

Try this:
=IF(C1="x","",IF(MATCH(A1&B1,A1:A4&B1:B4,0),"DUP", ""))

ctrl+shift+enter, not just enter


"Toppers" wrote:

This is a question from "Dan the Man" which I failed completely to address to
I'm admitting defeat and calling on the experts for help.

Dan wants the formula below modified so that if there is an "x" in column C
then the data should not be included in the calculation of duplicates.

In the sample 1 below, Kent Clark would count as a duplicate ("Duplicate
Names found") but Harry Potter would not. In sample 2 "No duplicate names
found" would result

Sample 1

Potter Harry x
Kent Clark
Kent Clark
Potter Harry x

Sample 2

Potter Harry x
Kent Clark x
Kent Clark x
Potter Harry x

Dan's formula:

=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")



My attempt included the following ...

=IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<""&$C$4:$C$3 500<"x",--(MATCH($A$4:$A$3500&$B$4:$B$3500&$C$4:$C$3500,$A$4 :$A$3500&$B$4:$B$3500&$C$4:$C$3500,0) .....

but if this is correct, I couldn't get the remaining part of the formula to
work i.e. I don't completely undertand how it works!

Thanks in advance.