Excel Filtering doesn't work with large files

Frequently when using large Excel files (60K rows by 40 columns), when
applying filter criteria, it does now always seem to filter correctly. Either
filters nothing, parts of the column etc, yet the filter is DEINFTELY applied
correctly. This occurs when filtering using both the drop down filter, as
well as the custom method.

The dropdowns in data|filter|autofilter have a limit of 1000 unique entries.
You can filter (using the custom option) on other values--but only 1000 will
show in the dropdown.

If you're filtering correctly, but some rows aren't included, make sure you
select your complete range before applying the data|Filter|autofilter.

If you let excel guess at the range and you have an empty row/column, xl may not
guess what you really want.

Debra Dalgleish has some work arounds for that 1000 limit:
http://www.contextures.com/xlautofilter02.html#Limits


Andy wrote:
>
> Frequently when using large Excel files (60K rows by 40 columns), when
> applying filter criteria, it does now always seem to filter correctly. Either
> filters nothing, parts of the column etc, yet the filter is DEINFTELY applied
> correctly. This occurs when filtering using both the drop down filter, as
> well as the custom method.

--

Dave Peterson

Was aware of the 1000 limit for the dropdown filter method. Even when
selecting one of the drop-down options or using the custom filter, the
programme still does not always filter properly.

"Dave Peterson" wrote:

> The dropdowns in data|filter|autofilter have a limit of 1000 unique entries.
> You can filter (using the custom option) on other values--but only 1000 will
> show in the dropdown.
>
> If you're filtering correctly, but some rows aren't included, make sure you
> select your complete range before applying the data|Filter|autofilter.
>
> If you let excel guess at the range and you have an empty row/column, xl may not
> guess what you really want.
>
> Debra Dalgleish has some work arounds for that 1000 limit:
> http://www.contextures.com/xlautofilter02.html#Limits
>
>
> Andy wrote:
> >
> > Frequently when using large Excel files (60K rows by 40 columns), when
> > applying filter criteria, it does now always seem to filter correctly. Either
> > filters nothing, parts of the column etc, yet the filter is DEINFTELY applied
> > correctly. This occurs when filtering using both the drop down filter, as
> > well as the custom method.
>
> --
>
> Dave Peterson
>

I'm not sure what that means. Does it mean that there are rows excluded from
the filter range?

Or does it mean that a value that you expected doesn't show up?

Maybe you have extra spaces in one of the values???

Andy wrote:
>
> Was aware of the 1000 limit for the dropdown filter method. Even when
> selecting one of the drop-down options or using the custom filter, the
> programme still does not always filter properly.
>
> "Dave Peterson" wrote:
>
> > The dropdowns in data|filter|autofilter have a limit of 1000 unique entries.
> > You can filter (using the custom option) on other values--but only 1000 will
> > show in the dropdown.
> >
> > If you're filtering correctly, but some rows aren't included, make sure you
> > select your complete range before applying the data|Filter|autofilter.
> >
> > If you let excel guess at the range and you have an empty row/column, xl may not
> > guess what you really want.
> >
> > Debra Dalgleish has some work arounds for that 1000 limit:
> > http://www.contextures.com/xlautofilter02.html#Limits
> >
> >
> > Andy wrote:
> > >
> > > Frequently when using large Excel files (60K rows by 40 columns), when
> > > applying filter criteria, it does now always seem to filter correctly. Either
> > > filters nothing, parts of the column etc, yet the filter is DEINFTELY applied
> > > correctly. This occurs when filtering using both the drop down filter, as
> > > well as the custom method.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Sorry if my response wasn't clear. When applying a filter (either from the
dropdown menu or by using the custom filter), the filtered results sometimes
excludes rows that I have filtered for, includes rows which I have not
filtered for, or on occassion does not alter the number of rows at all. No
apparent reason nor consistency...

Hope this is a little clearer.

"Dave Peterson" wrote:

> I'm not sure what that means. Does it mean that there are rows excluded from
> the filter range?
>
> Or does it mean that a value that you expected doesn't show up?
>
> Maybe you have extra spaces in one of the values???
>
> Andy wrote:
> >
> > Was aware of the 1000 limit for the dropdown filter method. Even when
> > selecting one of the drop-down options or using the custom filter, the
> > programme still does not always filter properly.
> >
> > "Dave Peterson" wrote:
> >
> > > The dropdowns in data|filter|autofilter have a limit of 1000 unique entries.
> > > You can filter (using the custom option) on other values--but only 1000 will
> > > show in the dropdown.
> > >
> > > If you're filtering correctly, but some rows aren't included, make sure you
> > > select your complete range before applying the data|Filter|autofilter.
> > >
> > > If you let excel guess at the range and you have an empty row/column, xl may not
> > > guess what you really want.
> > >
> > > Debra Dalgleish has some work arounds for that 1000 limit:
> > > http://www.contextures.com/xlautofilter02.html#Limits
> > >
> > >
> > > Andy wrote:
> > > >
> > > > Frequently when using large Excel files (60K rows by 40 columns), when
> > > > applying filter criteria, it does now always seem to filter correctly. Either
> > > > filters nothing, parts of the column etc, yet the filter is DEINFTELY applied
> > > > correctly. This occurs when filtering using both the drop down filter, as
> > > > well as the custom method.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Sounds like the excluded rows may have trailing or leading spaces in the
filter column cell.

Alan Beban
Andy wrote:
> Sorry if my response wasn't clear. When applying a filter (either from the
> dropdown menu or by using the custom filter), the filtered results sometimes
> excludes rows that I have filtered for, includes rows which I have not
> filtered for, or on occassion does not alter the number of rows at all. No
> apparent reason nor consistency...
>
> Hope this is a little clearer.
>
> "Dave Peterson" wrote:
>
>
>>I'm not sure what that means. Does it mean that there are rows excluded from
>>the filter range?
>>
>>Or does it mean that a value that you expected doesn't show up?
>>
>>Maybe you have extra spaces in one of the values???
>>
>>Andy wrote:
>>
>>>Was aware of the 1000 limit for the dropdown filter method. Even when
>>>selecting one of the drop-down options or using the custom filter, the
>>>programme still does not always filter properly.
>>>
>>>"Dave Peterson" wrote:
>>>
>>>
>>>>The dropdowns in data|filter|autofilter have a limit of 1000 unique entries.
>>>>You can filter (using the custom option) on other values--but only 1000 will
>>>>show in the dropdown.
>>>>
>>>>If you're filtering correctly, but some rows aren't included, make sure you
>>>>select your complete range before applying the data|Filter|autofilter.
>>>>
>>>>If you let excel guess at the range and you have an empty row/column, xl may not
>>>>guess what you really want.
>>>>
>>>>Debra Dalgleish has some work arounds for that 1000 limit:
>>>>http://www.contextures.com/xlautofilter02.html#Limits
>>>>
>>>>
>>>>Andy wrote:
>>>>
>>>>>Frequently when using large Excel files (60K rows by 40 columns), when
>>>>>applying filter criteria, it does now always seem to filter correctly. Either
>>>>>filters nothing, parts of the column etc, yet the filter is DEINFTELY applied
>>>>>correctly. This occurs when filtering using both the drop down filter, as
>>>>>well as the custom method.
>>>>
>>>>--
>>>>
>>>>Dave Peterson
>>>>
>>
>>--
>>
>>Dave Peterson
>>

If it's not the extra spaces (or even different values like:
hello vs. he11o
(first has ELLs, second has Ones)

Then I'd double check the autofilter range. Either select the whole range first
(bottom right through top left) and remove the filter, then reapply.

Or even do this:

Alt-f11 (to get to the VBE)
ctrl-g (to see the immediate window)

?activesheet.autofilter.range.address

Does that return all the rows in what you think is your filtered range?

If no, then remove and reapply that filter.

If yes, what are you filtering on?

A simple value?
If yes, type that simple value in a cell (I'll use A1)
find one of the cells that's trouble (I'll use X99)

Then put
=a1=x99
in another cell.

If that comes back false, then you don't have an exact match?




Andy wrote:
>
> Sorry if my response wasn't clear. When applying a filter (either from the
> dropdown menu or by using the custom filter), the filtered results sometimes
> excludes rows that I have filtered for, includes rows which I have not
> filtered for, or on occassion does not alter the number of rows at all. No
> apparent reason nor consistency...
>
> Hope this is a little clearer.
>
> "Dave Peterson" wrote:
>
> > I'm not sure what that means. Does it mean that there are rows excluded from
> > the filter range?
> >
> > Or does it mean that a value that you expected doesn't show up?
> >
> > Maybe you have extra spaces in one of the values???
> >
> > Andy wrote:
> > >
> > > Was aware of the 1000 limit for the dropdown filter method. Even when
> > > selecting one of the drop-down options or using the custom filter, the
> > > programme still does not always filter properly.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > The dropdowns in data|filter|autofilter have a limit of 1000 unique entries.
> > > > You can filter (using the custom option) on other values--but only 1000 will
> > > > show in the dropdown.
> > > >
> > > > If you're filtering correctly, but some rows aren't included, make sure you
> > > > select your complete range before applying the data|Filter|autofilter.
> > > >
> > > > If you let excel guess at the range and you have an empty row/column, xl may not
> > > > guess what you really want.
> > > >
> > > > Debra Dalgleish has some work arounds for that 1000 limit:
> > > > http://www.contextures.com/xlautofilter02.html#Limits
> > > >
> > > >
> > > > Andy wrote:
> > > > >
> > > > > Frequently when using large Excel files (60K rows by 40 columns), when
> > > > > applying filter criteria, it does now always seem to filter correctly. Either
> > > > > filters nothing, parts of the column etc, yet the filter is DEINFTELY applied
> > > > > correctly. This occurs when filtering using both the drop down filter, as
> > > > > well as the custom method.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson