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?



[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]N Column[/TD]
[TD]O Column[/TD]
[/TR]
[TR]
[TD]=COUNTIF(N:N,N5441)=1
Returns False[/TD]
[TD]Trim(05441)[/TD]
[TD]89365K[/TD]
[/TR]
[TR]
[TD]=COUNTIF(N:N,N5442)=1
Returns as False[/TD]
[TD]Trim(O5442)[/TD]
[TD]89365K[/TD]
[/TR]
[TR]
[TD]=COUNTIF(N:N,N5443)=1
Returns as True[/TD]
[TD]Trim(O5443)[/TD]
[TD]002567[/TD]
[/TR]
[TR]
[TD]=COUNTIF(N:N,N5444)=1
Returns as True[/TD]
[TD]Trim(O5444)[/TD]
[TD]002567[/TD]
[/TR]
</tbody>[/TABLE]

 

[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