ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Auto Filter Help (https://www.excelbanter.com/excel-discussion-misc-queries/209455-auto-filter-help.html)

dls61721

Auto Filter Help
 
I have a spreadsheet with 5 columns (A-E) and almost 26,000 rows. I found a
problem yesterday when trying to filter a column. I tried to filter out a
date of 11/2/2008 and no records were filtered with that date. Other dates
work, but in reviewing the drop down list on the header I also noted other
dates missing. A review of all records did however, show them present. In
an attempt to determine if they were entered incorrectly I sorted by that
column and the missing dates show up in date order properly. I've tried
re-entering those dates, changing to serial number and back to date and even
copied formatting from working rows, but to no avail. Is there a size limit
to auto filter? Any suggestions other than putting in Access will be greatly
appreciated:)

Pete_UK

Auto Filter Help
 
With Excel 2003 and earlier, there is a limit to the number of unique
items that can be displayed in the filter drop-down - this is 1000
unique items. You can still filter by using Custom ... and then
selecting Less than, or Greater than etc. For dates, you could put a
formula in a helper column which just gave you the month, for example,
then by selecting March on that column you would see more of your
dates in the filter drop-down of the date column.

Hope this helps.

Pete

On Nov 7, 6:29*pm, dls61721
wrote:
I have a spreadsheet with 5 columns (A-E) and almost 26,000 rows. *I found a
problem yesterday when trying to filter a column. *I tried to filter out a
date of 11/2/2008 and no records were filtered with that date. *Other dates
work, but in reviewing the drop down list on the header I also noted other
dates missing. *A review of all records did however, show them present. *In
an attempt to determine if they were entered incorrectly I sorted by that
column and the missing dates show up in date order properly. *I've tried
re-entering those dates, changing to serial number and back to date and even
copied formatting from working rows, but to no avail. *Is there a size limit
to auto filter? *Any suggestions other than putting in Access will be greatly
appreciated:)



dls61721

Auto Filter Help
 
Please disregard this post. I broke the cardinal rule and didn't look for
the answer in previous posts/replies. I found my answer and the answer was
that the list is populated only by the first 1,000 entries. I can and have
now used the custom option in auto filter and the data shows just fine.
Please accept my apologies:)

"dls61721" wrote:

I have a spreadsheet with 5 columns (A-E) and almost 26,000 rows. I found a
problem yesterday when trying to filter a column. I tried to filter out a
date of 11/2/2008 and no records were filtered with that date. Other dates
work, but in reviewing the drop down list on the header I also noted other
dates missing. A review of all records did however, show them present. In
an attempt to determine if they were entered incorrectly I sorted by that
column and the missing dates show up in date order properly. I've tried
re-entering those dates, changing to serial number and back to date and even
copied formatting from working rows, but to no avail. Is there a size limit
to auto filter? Any suggestions other than putting in Access will be greatly
appreciated:)



All times are GMT +1. The time now is 12:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com