AutoFilter list limit (Excel2000)

Topic

New Reply

I have a list of 35,487 names of bars and restuarants.
When I use Data-Filter-Autofilter the list does not include certain entries.
In particular, the dropdown does not show any entries beginning with the letter X, although there are a few (but not all) entries beginning with Y and Z.
I looked at Excel specifications but could not see anything on a limit to number of entries in an Autofilter list.
Anyone have any insights???

regards

zeddy

Reply | Quote

Replies

AutoFilter has a limit of 1000 unique entries. See XL: Not All List Items Are Displayed in the AutoFilter List.

(And of course, X-rated bars will be omitted to protect the innocent )

Reply | Quote

An autofilter list is only 1000 entries (at least in XL97). I don’t think it is different in XL2000.

No matter how many entries are unique only 1000 will be shown in an autofilter.

Some workarounds is to subgroup them (as a user I hate to scroll through over 1,000 UNIQUE entries)
Make a column of the first 1 or first 2 letters of the name. [=left(a1,2)] and copy it down the column. Then then the user can first select a subset, then go to the main list

Or make a sub group based on location, or something else to limit. You can even use multiple subgroups.

I, personally, for example, would rather make 3 selections from groups of 33 than to make 1 selection from a group of 36,000.

Steve

Reply | Quote

Many thanks for the info.
It isn’t mentioned in any of the many Excel books I have.
I liked the suggestion for grouping by letter!

zeddy

Reply | Quote