Columns and Conditional Formattin
 

I am using =countif(a:a, a1)1 to look for duplicate values in a spread sheet
and I have copied this formula to the end of the list. However the sheet in
question is updated by a team of about 10 and is constantly growing. Is it
possible to assign this condition to the entire column so that I don't have
to coninuously keep updating it?

As well does anyone know a formula that would look for duplicates in one
column however use the value in another column as a condition as well. For
example, I have invoice numbers in colum A which I use =countif(a:a, a1)1 to
look for duplicates. However, some duplicates are allowed as I may have two
vendors who will use the same invoice number. Therefore if there is a
duplicate in column A, it will not be returned as a duplicate since the
values in column C are different.

Any ideas would be helpful.

Thanks,

Brent

Columns and Conditional Formattin
 

=SUMPRODUCT(--($A$1:$A$1000=A1),--($C$1:$C$1000=C1))1

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Brent" wrote in message
...
I am using =countif(a:a, a1)1 to look for duplicate values in a spread
sheet
and I have copied this formula to the end of the list. However the sheet
in
question is updated by a team of about 10 and is constantly growing. Is
it
possible to assign this condition to the entire column so that I don't
have
to coninuously keep updating it?

As well does anyone know a formula that would look for duplicates in one
column however use the value in another column as a condition as well.
For
example, I have invoice numbers in colum A which I use =countif(a:a, a1)1
to
look for duplicates. However, some duplicates are allowed as I may have
two
vendors who will use the same invoice number. Therefore if there is a
duplicate in column A, it will not be returned as a duplicate since the
values in column C are different.

Any ideas would be helpful.

Thanks,

Brent

Columns and Conditional Formattin
 

Hi Brent

I would create a couple of named ranges
Insertnamedefine
Name Invoices Refers to =$A$1:INDEX($A:$A,COUNTA($A:$A))
Name Vendors Refers to =$C$1:INDEX($A:$A,COUNTA($A:$A))

Then use the formula
=SUMPROODUCT(--(Invoices=A1),--(Customers=C1))1
for your Conditional Formatting

A far as setting the formatting for the whole column, you could do that, but
it might be somewhat excessive.

Assuming your Conditional Formatting is set up in Column D and if you think
you may have 10,000 rows eventually, then copy the format from D1select
cells D2:D10000Paste SpecialFormats

--
Regards
Roger Govier



"Brent" wrote in message
...
I am using =countif(a:a, a1)1 to look for duplicate values in a spread
sheet
and I have copied this formula to the end of the list. However the sheet
in
question is updated by a team of about 10 and is constantly growing. Is
it
possible to assign this condition to the entire column so that I don't
have
to coninuously keep updating it?

As well does anyone know a formula that would look for duplicates in one
column however use the value in another column as a condition as well.
For
example, I have invoice numbers in colum A which I use =countif(a:a, a1)1
to
look for duplicates. However, some duplicates are allowed as I may have
two
vendors who will use the same invoice number. Therefore if there is a
duplicate in column A, it will not be returned as a duplicate since the
values in column C are different.

Any ideas would be helpful.

Thanks,

Brent