filter for unique records?

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi,

I have a simple spreadsheet that was populated by importing ascii data.
Everything looks normal, but when I attempt to use advance filter for unique
records only, it has no effect. Can anyone think of any reason why this
shouldn't work? It's a simple thing, but very annoying. I'm using excel
2002, SP3.

TIA,

Jim
 
Are you filtering on a single column?

Are you sure it's not working? Maybe there's a subtle difference
(trailing/leading/multiple embedded spaces???)
 
Hi Pete,

yes, I'm filtering on a single column. And there sure doesn't APPEAR to be
any subtle difference; is there a way for me to check those items you've
mentioned?

thanks

Jim
 
Jim

What type of data do you have?

Text? Numeric? Dates?

To look for extra spaces try =LEN(cellref) in an unused column and copy down.

Or =ISNUMBER(cellref) and copy down.


Gord Dibben MS Excel MVP

Hi Pete,

yes, I'm filtering on a single column. And there sure doesn't APPEAR to be
any subtle difference; is there a way for me to check those items you've
mentioned?

thanks

Jim
Click to expand...
 
If you filter in place, you'll be able to pick out two cells that look the same
to you, but show up as unique entries (just by eyeballing the visible cells).

Pick out a couple of cells (say A4 and A887) and in an empty cell, put:
=a4=a887
This will come back True if they match. False if they don't.

You can use:
=len(a4)&"--"&len(a887)
to see if the length's differ.

and
=trim(a4)=trim(a887)
to see if trimming any spaces would help.

Chip Pearson has a very nice addin that will help determine what is in each
cell:
http://www.cpearson.com/excel/CellView.aspx
Hi Pete,

yes, I'm filtering on a single column. And there sure doesn't APPEAR to be
any subtle difference; is there a way for me to check those items you've
mentioned?

thanks

Jim
Click to expand...
 
Dave and Gord,

thanks for the tips...problem solved. There were indeed some characters in
there that I couldn't see that were making the difference. Thanks again.

Jim
 
Back
Top