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

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

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

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)

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

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

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

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)

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

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

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)

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

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)

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

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)

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

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)

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)

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

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)

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

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)