Results 1 to 4 of 4
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,831
    Thanks
    136
    Thanked 484 Times in 461 Posts

    AutoFilter list limit (Excel2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AutoFilter list limit (Excel2000)

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AutoFilter list limit (Excel2000)

    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

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,831
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: AutoFilter list limit (Excel2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •