Countif doesn't recognize some cells

[Bram1988]

Excel 2007
Windows 8

Hi all,

For this project i need to analyze clusters of data. My first step is to remove all none duplicates.
I have created this formula =COUNTIF(N:N,N1)=1, this goes all the way down to =COUNTIF(N:N,N5443)=1. My plan is to remove all rows that return a "True" to delete all non-duplicates.

I'm noticing, although 90% is recognized correctly, 10% is not. Some formulas return as true, when clearly there are duplicate values.
The N Column is trimmed, to correct for spaces.

Does anyone have an idea how to solve this, or what the pitfalls with countif formulas usually are in this situation?

	N Column	O Column
=COUNTIF(N:N,N5441)=1 Returns False	Trim(05441)	89365K
=COUNTIF(N:N,N5442)=1 Returns as False	Trim(O5442)	89365K
=COUNTIF(N:N,N5443)=1 Returns as True	Trim(O5443)	002567
=COUNTIF(N:N,N5444)=1 Returns as True	Trim(O5444)	002567

<tbody>
</tbody>

 

[DILIPandey]

Hi Bram,

There are some characters which look like spaces but they are not and hence they can't go after applying TRIM function.
Can you check and confirm ?



Regards,
DILIPandey

 

[Bram1988]

Hi Bram,

There are some characters which look like spaces but they are not and hence they can't go after applying TRIM function.
Can you check and confirm ?



Regards,
DILIPandey

You are totally right! It seems some " invisible" characters are still there. Is there any way to remove those without manually checking 5000 cells?

 

[Snakehips]

Bram1988,

Just in case there are some 'invisible' control characters in O maybe try...

=TRIM(CLEAN(O1)) in N1

Or, to be sure that you catch Char(160) if it exists then try.....

=SUBSTITUTE(TRIM(CLEAN(O1)),CHAR(160),"")

Hope that helps.

 

[Bram1988]

Oke, This must have been a newbie question for the both of you, but i'm so gratefull. This worked perfectly!
I trusted the trim function to do it's job not expecting other character to still be there.
Thanks a lot!

 

[DILIPandey]

You are welcome Bram.
Thx to Tony for extending a helping hand.

Regards,
DILIPandey