Hi Toppers!

I tried the formula but couldn't get it to work on all testing parameters
given the limitations you described. Hopefully someone will chime in (like
Bob or Roger) with another idea that we haven't thought of. Ultimately, the
master spreadsheet wil be of a longer range that 10, and I probably need it
to go to at least 5000.

Hopefully the Excel Gods will come up with an answer!

Dan

"Toppers" wrote:

=IF(SUM(IF(RIGHT($A$4:$A$10&$B$4:$B$10&$C$4:$C$10) <"x",--(MATCH($A$4:$A$10&$B$4:$B$10,$A$4:$A$10&$B$4:$B$10 ,0)=ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1),0))=SUM(--(($A$4:$A$10<"")+($B$4:$B$10<"")0))-COUNTIF($C$4:$C$10,"x"),"No
Duplicate Names Found", "Duplicate Names Found")

This APPEARS to work BUT the proviso is that the range is limited to number
of non-blank entries i.e if I change $10 to $11 I get a wrong result!
[because of ROW($A$4:$A$10)-MIN(ROW($B$4:$B$10))+1)?]

And I cannot work how (or if) the "$A$4:$A$10&$B$4:$B$10&$C$4:$C$10" string
can be enabled using dynamic ranges.

"Toppers" wrote:

Roger,
Unfortunately no!

With reference to my sample below, the original test is that if there is any
match of A & B e.g. rows 5 & 6, then the result is "Duplicates Found" ; the
original formula would also have included 4 & 7 as it doesn't test for "x" in
column C. It simply reports that there are duplicates NOT what they are
(which is done using CF).

The revised test is to add the condition that if column C ="x" where there
is a corresponding A & B match i.e. 4 & 7, then treat this as a non-match
(ignore it). Hence my logic of concatenating A,B,C in the MATCH statement:
what I don't full understand is this part of the formula ...

...=ROW(A4:B3500)-MIN(ROW(B4:B3500))+1),0))=SUM(--((A4:A3500<"")+(B4:B3500<"")0))

and therefore how to adjust it if the A,B,C MATCH change I tried is the
correct way. It appears the logical way to me!

A B C

4 Potter Harry x
5 Kent Clark
6 Kent Clark
7 Potter Harry x





"Roger Govier" wrote:

Hi Toppers

Does the array entered formula

{=IF(C4="x","No
Duplicate",IF(SUM(IF($A$4:$A$3500&$B$4:$B$3500<"" ,--(MATCH($A$4:$A$3500&$B$4:$B$3500,$A$4:$A$3500&$B$4 :$B$3500,0)=ROW($A$4:$B$3500)-MIN(ROW($B$4:$B$3500))+1),0))=SUM(--(($A$4:$A$3500<"")+($B$4:$B$3500<"")0)),"No
Duplicate Names Found", "Duplicate Names Found"))}

provide what is required
--
Regards
Roger Govier



"Toppers" wrote in message
...
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.