Limit of records when using auto filter

[JennD]

Does anyone know of a limit of 1000 unique records when using an autofilter?
I have a worksheet with over 12,000 records of duplicate IDs that have to be
filtered to find bonus qualifiers and last month I had 1097 unique IDs that
needed to be filtered and processed. When using the filter to search for the
IDs it would stop at the 1000th ID and not show me the IDs beyond that at
all. I could only get to them by scrolling to the bottom, but I really need
to filter these to find out if all the criteria is met for the bonus.
Needless to say, we missed 97 unique IDs that could nave been paid out as
bonuses last month and I would like to find out how this could have happened.
Please help if you can.

 

[Paul B]

JennD, that is correct, have a look here for details and a workaround

http://www.contextures.com/xlautofilter02.html


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Michael]

Hi JennD. I'm sorry to say that auto filtering a single column is limited as
you have found out. Perhaps you could split the single column into two or
more for filtering purposes.

 

[Dave Peterson]

You could still filter using custom--it's just that the dropdown is limited to
1000 unique entries.

Debra Dalgleish has some workarounds:
http://contextures.com/xlautofilter02.html#Limits

 

[JennD]

Thanks Michael. I sure did find out . . . the hard way.
I thank you for your response, I had no idea that there was a 1000 limit on
the filter.
What doesn't kill you makes you stronger, right!!

 

[Michael]

Thanks for the reply, JennD. I think that Excel 12 will remove that
limitation. Please see Paul B's response for some workarounds.