SOLVED
Home

Conditional Format w/ Text Exception

%3CLINGO-SUB%20id%3D%22lingo-sub-852068%22%20slang%3D%22en-US%22%3EConditional%20Format%20w%2F%20Text%20Exception%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852068%22%20slang%3D%22en-US%22%3E%3CP%3EHaving%20a%20tough%20time%20figuring%20this%20one%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20reports%20in%20a%20sheet.%20One%20column%20is%20full%20of%20report%20numbers.%20I%20want%20to%20conditionally%20format%2C%20highlight%20any%20duplicate%20report%20number%2C%20EXCEPT%20if%20the%20cell%20says%20%22DRAFT%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20%3Dcountif(%24C%3A%24C%2C%22%26lt%3B%26gt%3B%20DRAFT%22)%20but%20that%20isn't%20working.%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-852068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econditionalformat%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-852387%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Format%20w%2F%20Text%20Exception%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852387%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407939%22%20target%3D%22_blank%22%3E%40CoryIDV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20whole%20column%20C%20and%20make%20a%20New%20Rule%20for%20conditional%20formatting%20using%20the%20formula%20given%20below%20and%20set%20the%20format%20as%20per%20your%20choice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DAND(COUNTIF(C%3AC%2CC1)%26gt%3B1%2CC1%26lt%3B%26gt%3B%22DRAFT%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-852931%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Format%20w%2F%20Text%20Exception%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20worked%2C%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20so%20I%20understand%20the%20formula%20a%20bit%20better%2C%20what%20is%20the%20purpose%20of%20the%20%22%26gt%3B1%22%20part%20of%20the%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853140%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Format%20w%2F%20Text%20Exception%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407939%22%20target%3D%22_blank%22%3E%40CoryIDV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%3C%2FP%3E%3CP%3E%26gt%3B1%20means%20that%20if%20there%20is%20only%20one%20occurrence%20of%20a%20value%2C%20the%20Countif%20formula%20will%20return%201%20and%20if%20there%20are%20more%20than%20one%20occurrences%20of%20the%20same%20value%2C%20the%20Countif%20formula%20will%20return%20a%20count%20which%20would%20be%20greater%20than%201.%20So%20a%20value%20will%20be%20considered%20as%20a%20duplicate%20value%20only%20if%20the%20Countif%20returns%20a%20count%20which%20is%20greater%20than%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20answers%20your%20query%2C%20please%20don't%20forget%20to%20mark%20the%20post%20with%20the%20proposed%20solution%20as%20an%20Accepted%20Answer%20which%20will%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853152%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Format%20w%2F%20Text%20Exception%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853152%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20explanation!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853213%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Format%20w%2F%20Text%20Exception%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407939%22%20target%3D%22_blank%22%3E%40CoryIDV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20problem!%3C%2FP%3E%3C%2FLINGO-BODY%3E
CoryIDV
New Contributor

Having a tough time figuring this one out. 

 

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". 

 

I've tried =countif($C:$C,"<> DRAFT") but that isn't working. Any ideas?

 

 

Thanks in advance!

5 Replies
Solution

@CoryIDV 

You may try this...

 

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.

 

=AND(COUNTIF(C:C,C1)>1,C1<>"DRAFT")

@Subodh_Tiwari_sktneer 

 

That worked, thanks!

 

Just so I understand the formula a bit better, what is the purpose of the ">1" part of the formula?

@CoryIDV 

You're welcome!

>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.

 

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.

Thanks for the explanation!

@CoryIDV 

No problem!

Related Conversations