Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does my advance filter only show one record? | Excel Discussion (Misc queries) | |||
Macro for Filter Switches ... Maybe??? | Excel Discussion (Misc queries) | |||
Filter Switches vs Macro? | Excel Discussion (Misc queries) | |||
about advance filter | New Users to Excel | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |