Jan 21 2021 05:38 PM
Jan 21 2021 05:38 PM
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
Jan 21 2021 09:58 PMSolution
@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:
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).
Jan 22 2021 03:30 PM
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!!!
Jan 22 2021 03:33 PM
Jan 22 2021 09:45 PM - edited Jan 22 2021 09:50 PM
@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).