advance filter
 

I am trying to isolate unique enteries from a list using the advance filter.
Each cell contains text. There are some cells that contain the same text,
but the spacing before the text is different. The advance filter does not
recognize these as unique enteries. I have tried to clear the formatting of
the cells and this doesn't work. Anyway, to make the advance filter to
ignore spacing and just look at text. Any other suggestions????

Thanks
DAN

Say your data is in column A, starting in row 2. In cell B2 use this formula:

=TRIM(A2)

and copy it down as far as needed. Then copy the range with the new
formulas, select the original cells, and choose Edt Paste Special Values
from the menu.

When finished, delete the formulas in column B.

Now your advanced filter should work just fine.



"DANmcc" wrote:

I am trying to isolate unique enteries from a list using the advance filter.
Each cell contains text. There are some cells that contain the same text,
but the spacing before the text is different. The advance filter does not
recognize these as unique enteries. I have tried to clear the formatting of
the cells and this doesn't work. Anyway, to make the advance filter to
ignore spacing and just look at text. Any other suggestions????

Thanks
DAN

Create a criteria area with a blank heading cell, and the following
formula in the cell below:

=AND(COUNTIF($C$1:$C2,C2)=1,C2=TRIM(C2))

Where column C contains the text that you want to filter.

When you run the Advance Filter, select the Blank heading cell, and the
cell with the formula, as the criteria area.

DANmcc wrote:
I am trying to isolate unique enteries from a list using the advance filter.
Each cell contains text. There are some cells that contain the same text,
but the spacing before the text is different. The advance filter does not
recognize these as unique enteries. I have tried to clear the formatting of
the cells and this doesn't work. Anyway, to make the advance filter to
ignore spacing and just look at text. Any other suggestions????

Thanks
DAN


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html