If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 D210000Paste 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 |
Thread Tools | |
Display Modes | |
|
|