values not appearing in autofilter list

[Guest]

Client is using Excel 03/WinXP. She received a data dump from another
application. She has autofilter turned on but some values do not appear. The
list is continuous. When using the custom autofilter, the values are found.
We can use the Find feature to find the values. Also, if that data is sorted
by that column, the values then do show up in the autofilter list. The values
she is looking for start with the word "inventory". Other values that begin
with the letter "i" do appear. We checked for spaces at the beginning of the
text - none. We even pasted the data onto a new sheet and pasted as values
and same thing. We are totally baffled. Any ideas?

 

[Guest]

Try running the CLEAN function on the offending values. It removes
non-printable characters which may not appear on the screen.

Dave

 

[Guest]

Autofilter only displays 1000 rows.
More info here:
http://www.contextures.com/xlautofilter02.html#Limits

 

[Pete_UK]

The pull-down list provided with the autofilter is only able to display
up to 1000 different values. It may be that before the data was sorted
the word inventory did not occur until after 1000 other values in that
column, whereas now it is in the first 1000 values - you might find,
however, that some other values were displayed before, but cannot be
seen in the list now.

Custom filter works on the complete set of data, so that is how you
were able to find the word.

One way around this is to use a helper column to extract the initial
letter in the column you are interested in, e.g.

=LEFT(B2,1)

copied down, if your data is in column B. Then you could apply a filter
on the initial letter and another filter on the column of interest.

Hope this helps.

Pete

 

[Dave Peterson]

Excel limits the number of entries that appear in that dropdown to 1000.

If your client has more than 1000 unique entries, then some won't appear in the
dropdown.

 

[Guest]

Does anyone know if 2007 will allow more?