highlighting duplicates...except it doesn't

[boombox]

I have a giant list of emails that I need to clean up, and I'd like to do it
by highlighing the duplicates. I use the conditional formatting tool, and it
only works for some. I can clearly see duplicates that it isn't
highlighting, though it will for others. It is just text, not merged or
hyperlinked or formatted any different. For the duplicates it doesn't
highlight, I can copy one dup and "find" (ctrl + f) the other. If it can
"find" both duplicates, and it will highlight others, why doesn't it work
consistently??

 

[Fred Smith]

It depends how you are checking for duplicates. The formula you are using
would have been useful. Assuming you are using a formula like:
=a1=a2
This will check the entire cell contents, including any spaces or
unprintable characters which may not display.

When you use ctrl-f, you're checking the cell to see if it contains the
string you entered. Trailing spaces, for example, would be ignored.

You can try using the Trim function, as in:
=trim(a1)=trim(a2)
That may solve your problem. If not, you'll need to go searching for
extraneous undisplayed characters.

Regards,
Fred.

 

[Gord Dibben]

I'm betting they are not duplicates.

You could have an extra space or an html non-breaking space.

Try TRIM ing the data in a helper column.

=TRIM(cellref) and copy down.

TRIM won't find non-breaking spaces.

Try to remove those using edit>replace

What: Alt + 0160........0160 on the NumPad

With: nothing


Gord Dibben MS Excel MVP