Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Files Acting Weird | Excel Discussion (Misc queries) | |||
problems with Sharing excel files | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
How do I open Excel 4.0 files in Office 2000? | Excel Discussion (Misc queries) | |||
Too many Excel files | Excel Discussion (Misc queries) |