Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition Formats for duplicates with additional criteria
On numerous occasions we have used the preset conditional format for
highlighting duplicate or unique values. However, we now have come across the need to highlight duplicate or unique values only if those values meet certain other criteria also. Such a task will lwill likely require a formula, but we are not sure how the Excel so effectively compares values in a large range to identify duplicate or unique values . If we knew how Excel was so efficiently performing these comparisons, we might could incorporate the functions for finding duplicate or unique values and then add additional tests in the same formula. Any ideas on how Excel can so quickly compare all the values in a large range to see if any of them are duplicates or uniques? Is there a function that can simulate that operation, yet also be combined with other functions in a larger formula? We would also like to, as an option, be able to find duplicate or unique values without regard to capitalization or for perhaps even for partial matches. Is this possible? Thank you for any help. |
#2
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition Formats for duplicates with additional criteria
http://cpearson.com/excel/Duplicates.aspx
On 18 Mar, 15:07, "Blue Max" wrote: On numerous occasions we have used the preset conditional format for highlighting duplicate or unique values. *However, we now have come across the need to highlight duplicate or unique values only if those values meet certain other criteria also. *Such a task will lwill likely require a formula, but we are not sure how the Excel so effectively compares values in a large range to identify duplicate or unique values . *If we knew how Excel was so efficiently performing these comparisons, we might could incorporate the functions for finding duplicate or unique values and then add additional tests in the same formula. Any ideas on how Excel can so quickly compare all the values in a large range to see if any of them are duplicates or uniques? *Is there a function that can simulate that operation, yet also be combined with other functions in a larger formula? We would also like to, as an option, be able to find duplicate or unique values without regard to capitalization or for perhaps even for partial matches. *Is this possible? Thank you for any help. |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
Condition Formats for duplicates with additional criteria
Hi,
Your first question - we can't see the code Microsoft uses so we can't tell you how they actually do it. Your second question - yes. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Blue Max" wrote: On numerous occasions we have used the preset conditional format for highlighting duplicate or unique values. However, we now have come across the need to highlight duplicate or unique values only if those values meet certain other criteria also. Such a task will lwill likely require a formula, but we are not sure how the Excel so effectively compares values in a large range to identify duplicate or unique values . If we knew how Excel was so efficiently performing these comparisons, we might could incorporate the functions for finding duplicate or unique values and then add additional tests in the same formula. Any ideas on how Excel can so quickly compare all the values in a large range to see if any of them are duplicates or uniques? Is there a function that can simulate that operation, yet also be combined with other functions in a larger formula? We would also like to, as an option, be able to find duplicate or unique values without regard to capitalization or for perhaps even for partial matches. Is this possible? Thank you for any help. |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
Condition Formats for duplicates with additional criteria
Hi,
Here is a case sensitive conditional format In 2007: 1. Highlight all the cells on the rows you want formatted, here assumed to be C1:C9. 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =SUMPRODUCT(--EXACT(C$1:C$9,C1))1 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Blue Max" wrote: On numerous occasions we have used the preset conditional format for highlighting duplicate or unique values. However, we now have come across the need to highlight duplicate or unique values only if those values meet certain other criteria also. Such a task will lwill likely require a formula, but we are not sure how the Excel so effectively compares values in a large range to identify duplicate or unique values . If we knew how Excel was so efficiently performing these comparisons, we might could incorporate the functions for finding duplicate or unique values and then add additional tests in the same formula. Any ideas on how Excel can so quickly compare all the values in a large range to see if any of them are duplicates or uniques? Is there a function that can simulate that operation, yet also be combined with other functions in a larger formula? We would also like to, as an option, be able to find duplicate or unique values without regard to capitalization or for perhaps even for partial matches. Is this possible? Thank you for any help. |
#5
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Condition Formats for duplicates with additional criteria
Thanks, Jarek, great link!
*************** "Jarek Kujawa" wrote in message ... http://cpearson.com/excel/Duplicates.aspx On 18 Mar, 15:07, "Blue Max" wrote: On numerous occasions we have used the preset conditional format for highlighting duplicate or unique values. However, we now have come across the need to highlight duplicate or unique values only if those values meet certain other criteria also. Such a task will lwill likely require a formula, but we are not sure how the Excel so effectively compares values in a large range to identify duplicate or unique values . If we knew how Excel was so efficiently performing these comparisons, we might could incorporate the functions for finding duplicate or unique values and then add additional tests in the same formula. Any ideas on how Excel can so quickly compare all the values in a large range to see if any of them are duplicates or uniques? Is there a function that can simulate that operation, yet also be combined with other functions in a larger formula? We would also like to, as an option, be able to find duplicate or unique values without regard to capitalization or for perhaps even for partial matches. Is this possible? Thank you for any help. |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
Condition Formats for duplicates with additional criteria
Thanks, Shane, a very good suggestion. Furthermore, I suspect we could make
this a case 'In-Sensitive' argument simply by forcing the text in the arrays to UPPERCASE(), correct? Thanks, Richard *************** "Shane Devenshire" wrote in message ... Hi, Here is a case sensitive conditional format In 2007: 1. Highlight all the cells on the rows you want formatted, here assumed to be C1:C9. 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =SUMPRODUCT(--EXACT(C$1:C$9,C1))1 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Blue Max" wrote: On numerous occasions we have used the preset conditional format for highlighting duplicate or unique values. However, we now have come across the need to highlight duplicate or unique values only if those values meet certain other criteria also. Such a task will lwill likely require a formula, but we are not sure how the Excel so effectively compares values in a large range to identify duplicate or unique values . If we knew how Excel was so efficiently performing these comparisons, we might could incorporate the functions for finding duplicate or unique values and then add additional tests in the same formula. Any ideas on how Excel can so quickly compare all the values in a large range to see if any of them are duplicates or uniques? Is there a function that can simulate that operation, yet also be combined with other functions in a larger formula? We would also like to, as an option, be able to find duplicate or unique values without regard to capitalization or for perhaps even for partial matches. Is this possible? Thank you for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condition Formats for duplicates with additional criteria | New Users to Excel | |||
If - additional criteria | Excel Discussion (Misc queries) | |||
Subtotal with additional criteria | Excel Worksheet Functions | |||
Can I retain the formats in a pivot when expanded to additional wo | Excel Discussion (Misc queries) | |||
2 condition Duplicates | Excel Worksheet Functions |