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