SOLVED

Conditional formatting in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2085186%22%20slang%3D%22en-US%22%3EConditional%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2085186%22%20slang%3D%22en-US%22%3E%3CP%3EI%20copied%20over%20a%20list%20of%20names%20from%20a%20webpage%20and%20pasted%20the%20data%20into%20a%20column%20in%20Excel.%26nbsp%3B%20I%20always%20use%20%22Paste%20Special%22%20%26gt%3B%20%22Match%20Destination%20formatting%22.%20Works%20great%20and%20the%20data%20is%20all%20there%20etc.%3C%2FP%3E%3CP%3EThis%20particular%20time%20I%20need%20to%20find%20all%20the%20duplicates%20in%20this%20list.%20I%20tried%20%22Conditional%20Formatting%22%26gt%3B%22Highlight%20Cells%20Rule%22%26gt%3B%22Duplicate%20Values%22...%20then%20I%20follow%20the%20%22format%20cells%20that%20contain%3A%20%22Duplicate%22%20values%20with%20%22light%20red%20fill%20with%20dark%20red%20text%22.%3C%2FP%3E%3CP%3ENothing%20happens....%20literally%20nothing%20changes%20at%20all!%3C%2FP%3E%3CP%3EIf%20I%20open%20a%20new%20tab%20and%20type%20in%20some%20test%20names%20by%20hand%20-%20and%20include%20duplicate%20names%20-%20the%20%22Conditional%20formatting%22%20works%20perfectly%20i.e..%20the%20duplicates%20have%20light%20red%20fill%20with%20dark%20red%20text.%3C%2FP%3E%3CP%3EI've%20formatted%20the%20cells%20alike%20(used%20both%20%22general%22%20and%20%22text%22)%2C%20and%20I%20have%20toggled%20the%20%22Lock%22%20option%20under%20the%20%22Protection%22%20tab%20-%20but%20nothing%20changes.%3C%2FP%3E%3CP%3ECould%20use%20some%20help%20please%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2085186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2085676%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2085676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F938386%22%20target%3D%22_blank%22%3E%40Brewster56%3C%2FA%3E%26nbsp%3BMost%20likely%2C%20the%20texts%20you%20copied%20from%20the%20web%20contains%20%22white%20space%22%20that%20isn't%20empty.%20These%20can%20be%20trailing%20spaces%20or%20other%20hidden%20characters.%3C%2FP%3E%3CP%3EFor%20a%20computer%20%22Name%22%20is%20not%20equal%20to%20%22Name%20%26nbsp%3B%20%22%20(i.e.%20%22Name%22%20plus%20three%20spaces)%20even%20though%20they%20may%20look%20the%20same%20in%20your%20Excel%20document.%20Locate%20two%20cell%20in%20the%20data%20you%20copied%20from%20the%20web%20that%20look%20the%20same%20to%20you.%20Let's%20say%20A2%20and%20A10%20seem%20to%20be%20duplicates.%20Type%20the%20following%20formula%20somewhere%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DA2%3DA10%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIf%20the%20result%20is%20FALSE%2C%20they%20ar%20NOT%20duplicates%20and%20conditional%20formatting%20will%20not%20highlight%20them.%3C%2FP%3E%3CP%3EYou%20can%20try%20functions%20like%20TRIM%2C%20CLEAN%20or%20SUBSTITUTE%20to%20clean-up%20the%20data.%20If%20this%20does%20not%20resolve%20the%20issue%2C%20perhaps%20you%20can%20upload%20a%20copy%20of%20the%20workbook%20(as%20long%20as%20it%20doesn't%20contain%20any%20private%20and%20confidential%20information).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2088831%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2088831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F938386%22%20target%3D%22_blank%22%3E%40Brewster56%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3ERiny%20van%20Eekelen%3C%2FA%3E%3C%2FP%3E%3CP%3EWow!!%20No%20wonder%20you%20are%20a%20Respected%20Contributor%20-%20it%20is%20exactly%20the%20issue%20you%20pointed%20out!!%3C%2FP%3E%3CP%3EI%20had%20absolutely%20no%20idea%20as%20all%20the%20text%20etc.%2C%20looks%20alike.%20I%20ran%20the%20formula%20you%20suggested%20and%20some%20came%20up%20%22TRUE%22%20and%20others%20came%20up%20%22FALSE%22.%3C%2FP%3E%3CP%3EI%20certainly%20learned%20something%20valuable%20from%20you%20today%20-%20which%20is%20good%20in%20and%20of%20itself.%20I%20am%20unfamiliar%20with%20the%20functions%20you%20suggested%20-%20but%20I%20will%20give%20them%20a%20go!!%3C%2FP%3E%3CP%3EMy%20respect%20to%20you%20as%20well%20-%20and%20many%20thanks!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2088841%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2088841%22%20slang%3D%22en-US%22%3EWow!!%20No%20wonder%20you%20are%20a%20Respected%20Contributor%20-%20it%20is%20exactly%20the%20issue%20you%20pointed%20out!!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20had%20absolutely%20no%20idea%20as%20all%20the%20text%20etc.%2C%20looks%20alike.%20I%20ran%20the%20formula%20you%20suggested%20and%20some%20came%20up%20%22TRUE%22%20and%20others%20came%20up%20%22FALSE%22.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20certainly%20learned%20something%20valuable%20from%20you%20today%20-%20which%20is%20good%20in%20and%20of%20itself.%20I%20am%20unfamiliar%20with%20the%20functions%20you%20suggested%20-%20but%20I%20will%20give%20them%20a%20go!!%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20respect%20to%20you%20as%20well%20-%20and%20many%20thanks!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2089190%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2089190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F938386%22%20target%3D%22_blank%22%3E%40Brewster56%3C%2FA%3E%26nbsp%3BGood%20luck!%20Come%20back%20here%20if%20you%20need%20more%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%3A%20Just%20after%20posting%20my%20response%20here%2C%20I%20replied%20to%20another%20question%20dealing%20with%20white-space.%20I%20added%20a%20workbook%20with%20a%20simple%20%22tool%22%20that%20helps%20identifying%20all%20the%20characters%20in%20your%20text.%20It%20uses%20one%20of%20the%20latest%20dynamic%20array%20functions%20(SEQUENCE).%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fused-xlookup-and-came-back-with-result-n-a-although-text-fields%2Fm-p%2F2089096%2Fhighlight%2Ffalse%23M86885%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fused-xlookup-and-came-back-with-result-n-a-although-text-fields%2Fm-p%2F2089096%2Fhighlight%2Ffalse%23M86885%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I copied over a list of names from a webpage and pasted the data into a column in Excel.  I always use "Paste Special" > "Match Destination formatting". Works great and the data is all there etc.

This particular time I need to find all the duplicates in this list. I tried "Conditional Formatting">"Highlight Cells Rule">"Duplicate Values"... then I follow the "format cells that contain: "Duplicate" values with "light red fill with dark red text".

Nothing happens.... literally nothing changes at all!

If I open a new tab and type in some test names by hand - and include duplicate names - the "Conditional formatting" works perfectly i.e.. the duplicates have light red fill with dark red text.

I've formatted the cells alike (used both "general" and "text"), and I have toggled the "Lock" option under the "Protection" tab - but nothing changes.

Could use some help please

6 Replies
Best Response confirmed by Brewster56 (New Contributor)
Solution

@Brewster56 Most likely, the texts you copied from the web contains "white space" that isn't empty. These can be trailing spaces or other hidden characters.

For a computer "Name" is not equal to "Name   " (i.e. "Name" plus three spaces) even though they may look the same in your Excel document. Locate two cell in the data you copied from the web that look the same to you. Let's say A2 and A10 seem to be duplicates. Type the following formula somewhere:

=A2=A10

If the result is FALSE, they ar NOT duplicates and conditional formatting will not highlight them.

You can try functions like TRIM, CLEAN or SUBSTITUTE to clean-up the data. If this does not resolve the issue, perhaps you can upload a copy of the workbook (as long as it doesn't contain any private and confidential information).

@Brewster56 

Riny van Eekelen

Wow!! No wonder you are a Respected Contributor - it is exactly the issue you pointed out!!

I had absolutely no idea as all the text etc., looks alike. I ran the formula you suggested and some came up "TRUE" and others came up "FALSE".

I certainly learned something valuable from you today - which is good in and of itself. I am unfamiliar with the functions you suggested - but I will give them a go!!

My respect to you as well - and many thanks!!!

Wow!! No wonder you are a Respected Contributor - it is exactly the issue you pointed out!!

I had absolutely no idea as all the text etc., looks alike. I ran the formula you suggested and some came up "TRUE" and others came up "FALSE".

I certainly learned something valuable from you today - which is good in and of itself. I am unfamiliar with the functions you suggested - but I will give them a go!!

My respect to you as well - and many thanks!!!

@Brewster56 Good luck! Come back here if you need more help.

 

PS: Just after posting my response here, I replied to another question dealing with white-space. I added a workbook with a simple "tool" that helps identifying all the characters in your text. It uses one of the latest dynamic array functions (SEQUENCE).

https://techcommunity.microsoft.com/t5/excel/used-xlookup-and-came-back-with-result-n-a-although-tex... 

 

@Riny_van_Eekelen 

You are awesome!!  Really...really appreciate all of this.  I'll check it all out.  I even appreciate the learning I'm experiencing.... 

Kindest regards!!!

@Brewster56 Get back here if you run into any trouble!