Columns and Conditional Formattin

[Guest]

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

 

[Bob Phillips]

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

 

[Roger Govier]

Hi Brent

I would create a couple of named ranges
Insert>name>define>
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 D1>select
cells D210000>Paste Special>Formats