Advanced Filter

[Alan]

Hi,
Is there any limit to the number of unique items that Advanced Filter
will find in a list? I have about 52,000 rows in a single column and it
shows 1500 or so as unique. I believe auto filter has a limit of around one
thousand, is there a similar restriction on Advanced? I've looked everywhere
I can think of and found no reference at all to limits.
Thanks in advance,
Alan

 

[Mike]

is there a similar restriction on Advanced?

No.

 

[Debra Dalgleish]

An AutoFilter dropdown list will only show 1000 entries. If your column
has more than 1000 unique items, they can be filtered, but they can't
all be displayed in the dropdown list.

To AutoFilter for an item that doesn't appear in the dropdown list, you
can choose Custom from the drop-down list, and type the criteria. Also,
there are a couple of workarounds here:

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

 

[Andy Brown]

Is there any limit to the number of unique items that Advanced Filter

will find in a list?

If there is, then it's not, as you say, widely documented. There is of
course the other side of the coin ; advance filtering (uniquely) 65535
unique records (one column) will keep XL2K on a 1.3 m/c busy for >5 minutes.
Probably best *not* to try this at home.

HTH,
Andy

 

[Alan]

I did that five minutes after I posted! Amazing how you think of these
things after the question has been asked. Went and made a coffee and it was
still buzzing away when I got back. Still, at least it proves the point.
Thanks to you all and for the very useful link from Debra,
Alan