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

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 A2:D100
goto menu FormatConditional Formatting
change Condition to Formula Is
add a formula of =$E21
click Format button
select Pattern tab
choose a colour
OK
OK

--
HTH

Bob Phillips

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

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

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" wrote:

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 A2:D100
goto menu FormatConditional Formatting
change Condition to Formula Is
add a formula of =$E21
click Format button
select Pattern tab
choose a colour
OK
OK

--
HTH

Bob Phillips

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

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

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)

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

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 A2:D100
goto menu FormatConditional Formatting
change Condition to Formula Is
add a formula of =$E21
click Format button
select Pattern tab
choose a colour
OK
OK

--
HTH

Bob Phillips

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

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

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" wrote:

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)

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

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 A2:D100
goto menu FormatConditional Formatting
change Condition to Formula Is
add a formula of =$E21
click Format button
select Pattern tab
choose a colour
OK
OK

--
HTH

Bob Phillips

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

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

Do you want to repeat that with different words?

--
HTH

Bob Phillips

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

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

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)

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

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 A2:D100
goto menu FormatConditional Formatting
change Condition to Formula Is
add a formula of =$E21
click Format button
select Pattern tab
choose a colour
OK
OK

--
HTH

Bob Phillips

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

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