+ Reply to Thread
Results 1 to 14 of 14

"Remove Duplicates" not working on a list unless I paste back and forth from Word.

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    MD, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Hi everyone.

    I'm finding myself in an odd situation where I have the solution to my own problem, but I have no idea why it's only working in this scenario.

    I'm working with a company's attendance sheet and they only want unique visitors. I've been using data from their program that exports to excel, and I've been pasting special 'values and formats' for all the data I get from them. Then I've used Remove Duplicates to filter down to the unique visitors.

    Thing is, somewhere between getting the information from different sources, Excel's now treating some entries that appear to be 100% identical as inequal. So, a list that says simply:

    Shoemaker, Janice
    Shoemaker, Janice

    Will not see these two as duplicates. I did the usual Trim and LEN checks, the formatting in each cell is identical, etc. And naturally, cell = cell returns False. My usual go-to trick when this happens is to copy this into Notepad and paste back in (special, if needed) until excel realizes they're the same. However, that's usually for formatting/'number as text' issues, and thus doesn't work in this case.

    The only thing that finally worked for me was to paste special => Unicode into Word, then paste special => Text back into excel. And only then it works, (cell = cell returns True) and that's great but uh... I'm baffled as to why.

    I'm hoping that the reports will come in consistent formatting to prevent future occurrences, but I don't like leaving the "why" of this problem unresolved. Any insight? Thanks for your help,

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,891

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    03-01-2013
    Location
    MD, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Done. Sorry I didn't do it previously, wasn't sure if it would require that. Names in it are faked.
    Attached Files Attached Files

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    The spaces are not all spaces. Copy your data into Word and then do a Find and Replace. In the Find field enter ^w and in the Replace With field enter a space. Replace All.

    I did this with your sample data and when copied back into Excel, the remove duplicates worked perfectly.

    Some of the spaces in the original data had a "Degree" symbol instead of a space.

  5. #5
    Registered User
    Join Date
    03-01-2013
    Location
    MD, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Quote Originally Posted by newdoverman View Post
    The spaces are not all spaces. Copy your data into Word and then do a Find and Replace. In the Find field enter ^w and in the Replace With field enter a space. Replace All.

    I did this with your sample data and when copied back into Excel, the remove duplicates worked perfectly.

    Some of the spaces in the original data had a "Degree" symbol instead of a space.
    I.. hrm. I mean, that's very odd/interesting, but I guess I have to ask... how would one know that? I mean, doing what you said about Finding in MS Word, all the spaces are 'found' when searching for ^w.. as are if you search for a space. I do see that searching for the space fails half the entries in Excel, but it still only appears to be a space no matter where I copy that phantom character.

    If there's a reason you knew to check for that, I'd be curious to know. Thanks again,

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    If you have enabled seeing formatting, sometimes the "odd" space shows as being "odd". I have had to do this so many times that whenever I have problems with imported data I automatically suspect that there is something wrong with spaces.

  7. #7
    Registered User
    Join Date
    04-13-2012
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    I would assume the "odd" space is a non-breaking space http://en.wikipedia.org/wiki/Non-breaking_space

    ETA: maybe not, on reflection.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    A non-breaking space is definitely one of the possibilities.

    I used to get data "dumps" from main-frame computers and spaces could be a lot of different things that were only visible as spaces but were other characters so, I just got into the habit of using Word and replacing using ^w which found nearly all the problem characters.
    Last edited by newdoverman; 03-04-2013 at 04:50 PM.

  9. #9
    Registered User
    Join Date
    03-01-2013
    Location
    MD, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Interesting... this was definitely as a result of a data dump, so I'll be mindful of that possibility whenever something odd crops up.

    The "see formatting" thing you mentioned is interesting. How does one go about that? I couldn't immediately find anything about that, and the viewing formulas option doesn't change it, naturally. Thanks guys,

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    By "seeing the formatting" I should have said that in Word you can Show or Hide formatting and other non printing symbols by clicking on the symbol that looks like a backwards facing P.

  11. #11
    Registered User
    Join Date
    07-21-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    9

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Resurrecting an old post, but I am encountering the exact same issue here, tho, it's for a game.
    Columns A/B are using Conditional Formatting and displaying the Duplicates.
    Columns D/E are the exact same paste from the exact same source, pasted in as "Values"
    I've pasted from Excel to Excel. Notepad to Excel. Wordpad to Excel. Word to Excel. Always doing the paste as Values.
    A/B works and highlights the Dupes, but when I try "Remove Duplicates" on D/E (or A/B) it always says that there are "No duplicate values found"

    EDIT:
    Of course, this is my 1st post here and I can't even do an Attachment or Links to my Dropbox/Drive to show the file. ugh. Now I have to post up on others just to get to.

    EDIT 2: Forgot to mention this is Office 2016
    Last edited by Vitae; 10-21-2019 at 12:36 PM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2301 (Windows 11 22H2 64-bit)
    Posts
    65,647

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  13. #13
    Registered User
    Join Date
    07-21-2019
    Location
    Florida
    MS-Off Ver
    2016
    Posts
    9

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    Understood. Sorry.
    1st timer on here. Figured since the question was similar (remove dupes not working) I thought it was ok.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,316

    Re: "Remove Duplicates" not working on a list unless I paste back and forth from Word.

    No problem You wont know unless some1 informs you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1