Counting duplicates

[Guest]

I need to be able to show any duplicates in a list it will look like this

A B C D

ROAD NAME NUMBER STATUS NUMBER OF TIMES APPEARED

I need to highlight the duplicates so that if a road name and a house in
that road keeps appearing with the same status will be either (Yes or no) i
can have in a column the number of times it has appeared and maybe highlight
its latest entry in a colour so for example Gerald Road No:14 appears for the
third time the latest entry has 3 in red in the D column. Ideally as they
type it in it will show them then.

This really has me stumped i have been looking at other macros or formulas
but i am still stuck
Many Thanks for looking

 

[Bob Phillips]

Add a formula in D2

=SUMPRODUCT(--($A$1:$A$100=A1),--($B$1:$B$100=B1)

to get the count.

Then use conditional formatting to highlight them

select A2100
goto menu Format>Conditional Formatting
change Condition to Formula Is
add a formula of =$E2>1
click Format button
select Pattern tab
choose a colour
OK
OK

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks for the reply Bob
I may be a bit stupid here but i cannot get it to work correctly or maybe i
explained myself wrong.
I am trying to get it so in the D column it will show if that the matching
entry in A and B ( the address) will show if it has been entered more than 2
times if in column c i have "yes" in it. so if it is more than 2 times i can
use a filter to list the address that meet these conditions. And the
highlighting the address if it appears more than once on the last entry.
Sorry it seems so complicated.
But your help is greatly appreicated

Thanks again

 

[Bob Phillips]

You need an extra condition it seems

=SUMPRODUCT(--($C$1:$C$100="Yes"),--($A$1:$A$100=A1),--($B$1:$B$100=B1)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob

It works great but is it possible to amend it so if the column that has YES
in is blank it ignores the count on the entry as the number appears in the
count when it has the same address but a blank in the yes column, although it
doesn't add this to the total.it just means on the filter they can select for
example all 3 entries but just show the latest so they won't have the same
address listed on the count several times? sorry to be a pain

Many thanks

 

[Bob Phillips]

Do you want to repeat that with different words?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Its not that i want to write it with different words that is just doesn't
count the blank cells that are next to the matching addresses.

e.g
A B C D E
Gerald road 14 Yes 3 01/02/04
gerald road 14 Yes 3 01/03/04
gerald road 14 01/04/04
gerald road 14 yes 3 01/05/06

I hope this is clearer i would ideally like for the count to go up so the
latest entry with the yes in C would count 1/05/06 3 in D and the first entry
(01/02/04) to say 1 in D. But even as it is ok just i would need it to not
count in Column D when C is blank but the road and number still matches. This
should make it easyier to seperate from all the other entries when i do a
filter and just look at anything with a count of 3 or more for all the
different roads.

Many thanks for your time Bob

 

[Bob Phillips]

Neil,

You have a knack of stringing together words that I understand into
sentences that I don't <G>

Here is a shot at what I think you mean though. Put this in D1 and copy down

=SUMPRODUCT(--($A$1:$A1=A1),--($B$1:$B1=B1),--($C$1:$C1="Yes"))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Yes sorry Bob i can see i am writing in a language only i seem to know!! The
code almost works it does what i need it to except that the count also apears
in column D when column C is empty ie no "yes" in it.
It doesn't include it in the count just displays what the current count is
it would be usefull for me if it only appeared when the yes is in column C.
But otherwise it is great

Thank you very much for the help

 

[Bob Phillips]

Neil,

This may not be exactly what you want, post back if not, but try

=IF(C1="Yes",SUMPRODUCT(--($A$1:$A1=A1),--($B$1:$B1=B1),--($C$1:$C1=C1)),"")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

That is perfect Bob thank you, is it possible to adapt it to count different
text in the C column such as "no" as well as the "yes" but in a seperate
count?

Thanks again it was a big big help

 

[Bob Phillips]

Shouldn't be difficult

=IF(C5="Yes",SUMPRODUCT(--($A$1:$A5=A5),--($B$1:$B5=B5),--($C$1:$C5="Yes")),
IF(C5="No",SUMPRODUCT(--($A$1:$A5=A5),--($B$1:$B5=B5),--($C$1:$C5="No")),"")
)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)