Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional sum matching two columns and a row | Excel Worksheet Functions | |||
Conditional Formatting 4 Columns | Excel Discussion (Misc queries) | |||
Conditional sum of columns | Excel Discussion (Misc queries) | |||
IS THERE A FAST WAY TO CHANGE THE ORDERS OF CONDITIONAL FORMATTIN. | Excel Discussion (Misc queries) | |||
Text Box formattin | Excel Discussion (Misc queries) |