Conditional Formatting and Duplicates: Seems to be skipping some

Hi everyone,

So i am checking a reading list for duplicates. I copied the list over from Word into Excel and have used conditional formatting to highlight in green any duplicates, and highlight in red any unique texts.

Whilst it has worked for the majority, it has skipped a few of the duplicates. I have attached the sheet and added orange cells in column b to show which examples of a couple of them.

To cross-check, I have been using the find and replace. When using find and replace, it shows that some rows in red are actually duplicates.

If anyone could help with this, it'd be much appreciated as I really cant miss any.

Thanks so much

Row 58 has a trailing space, but row 116 (which looks exactly the same) does not. The same applies to rows 60 and 96.

You will need to remove those trailing spaces, for example using TRIM in another column.

Hope this helps.

Pete

Some of the cells have trailing spaces which is causing the problem.

Thanks for this.

So, I have used TRIM, but it hasnt fixed the issue...

I have uploaded it with TRIM used in column b.

Am I using it incorrectly?

It's actually a non-breaking space (character 160) rather than a normal space, so trim will not work.
Do a search & replace in the find what box put 0160 Whilst using the number pad & holding down the Alt key at the same time.

Actually, it is a non-breaking space character (with an ASCII code of 160) at the end of A60, rather than the usual space character (which has a code of 32). You can use Find & Replace on column A to remove this character directly - in the Find What part, you should hold down the Alt key and type 0160 on the numeric keypad to generate the character.

Hope this helps.

Pete

Thanks for the advice.

I have followed the instructions and something has happened, but I cannot see any character.
Also what should I replace it with? Should I leave that blank?

Yes, you need to leave the replace with empty.

Ok, thanks so much everyone :-).
I will do some more to it and see if it has worked.

Glad to help & thanks for the feedback.