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

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

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.

Originally Posted by newdoverman

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,

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.

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

ETA: maybe not, on reflection.

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,

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.

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