Conversation Re: Conditional Format w/ Text Exception in Excelhttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/852931#M39912
<P><LI-USER uid="394231"></LI-USER>&nbsp;</P><P>&nbsp;</P><P>That worked, thanks!</P><P>&nbsp;</P><P>Just so I understand the formula a bit better, what is the purpose of the "&gt;1" part of the formula?</P>Thu, 12 Sep 2019 17:41:24 GMTCoryIDV2019-09-12T17:41:24ZConditional Format w/ Text Exceptionhttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/852068#M39901
<P>Having a tough time figuring this one out.&nbsp;</P><P>&nbsp;</P><P>I have a list of reports in a sheet. One column is full of report numbers. I want to conditionally format, highlight any duplicate report number, EXCEPT if the cell says "DRAFT".&nbsp;</P><P>&nbsp;</P><P>I've tried =countif($C:$C,"&lt;&gt; DRAFT") but that isn't working. Any ideas?</P><P>&nbsp;</P><P>&nbsp;</P><P>Thanks in advance!</P>Thu, 12 Sep 2019 14:20:46 GMThttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/852068#M39901CoryIDV2019-09-12T14:20:46ZRe: Conditional Format w/ Text Exceptionhttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/852387#M39903
<P><LI-USER uid="407939"></LI-USER>&nbsp;</P><P>You may try this...</P><P>&nbsp;</P><P>Select the whole column C and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.</P><P>&nbsp;</P><P><STRONG>=AND(COUNTIF(C:C,C1)&gt;1,C1&lt;&gt;"DRAFT")</STRONG></P>Thu, 12 Sep 2019 15:25:59 GMThttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/852387#M39903Subodh_Tiwari_sktneer2019-09-12T15:25:59ZRe: Conditional Format w/ Text Exceptionhttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/852931#M39912
<P><LI-USER uid="394231"></LI-USER>&nbsp;</P><P>&nbsp;</P><P>That worked, thanks!</P><P>&nbsp;</P><P>Just so I understand the formula a bit better, what is the purpose of the "&gt;1" part of the formula?</P>Thu, 12 Sep 2019 17:41:24 GMThttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/852931#M39912CoryIDV2019-09-12T17:41:24ZRe: Conditional Format w/ Text Exceptionhttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/853140#M39915
<P><LI-USER uid="407939"></LI-USER>&nbsp;</P><P>You're welcome!</P><P>&gt;1 means that if there is only one occurrence of a value, the Countif formula will return 1 and if there are more than one occurrences of the same value, the Countif formula will return a count which would be greater than 1. So a value will be considered as a duplicate value only if the Countif returns a count which is greater than 1.</P><P>&nbsp;</P><P>If that answers your query, please don't forget to mark the post with the proposed solution as an Accepted Answer which will mark your question as Solved.</P>Thu, 12 Sep 2019 18:14:00 GMThttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/853140#M39915Subodh_Tiwari_sktneer2019-09-12T18:14:00ZRe: Conditional Format w/ Text Exceptionhttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/853152#M39916
Thanks for the explanation!Thu, 12 Sep 2019 18:19:09 GMThttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/853152#M39916CoryIDV2019-09-12T18:19:09ZRe: Conditional Format w/ Text Exceptionhttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/853213#M39920
<P><LI-USER uid="407939"></LI-USER>&nbsp;</P><P>No problem!</P>Thu, 12 Sep 2019 18:33:20 GMThttps://techcommunity.microsoft.com/t5/excel/conditional-format-w-text-exception/m-p/853213#M39920Subodh_Tiwari_sktneer2019-09-12T18:33:20Z