Attn: Bob Phillips (or any expert!)... help with formula

[Toppers]

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$3500<>"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.

 

[Roger Govier]

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

 

[Toppers]

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

 

[Toppers]

=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.

 

[Dan the Man]

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

=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.

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

 

[Teethless mama]

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

ctrl+shift+enter, not just enter

 

[Dan the Man]

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

 

[Roger Govier]

Hi John

I know you have excellent knowledge of Excel, and therefore there must be
something that I am not understanding about the problem, but
with my formula and your data, I get
No Duplicates
Duplicate Names Found
Duplicate Names Found
No Duplicates

which is what I thought your wanted.

Your formula returns me No Duplicates Found for all 4 lines.

Note, my No Duplicates result, is the result of the first test, i.e. if
there is an X in column C, don't carry out any further testing.
The message can be changed to anything else or Null.
Or is it that you need to show some form of message, that the result is a
duplicate, but an allowed duplicate? If so then my formula obviously would
not work.

--
Regards
Roger Govier

=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.

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