Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
values not appearing in autofilter list
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? -- maryj |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
values not appearing in autofilter list
Try running the CLEAN function on the offending values. It removes
non-printable characters which may not appear on the screen. Dave -- Brevity is the soul of wit. "maryj" wrote: 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? -- maryj |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
values not appearing in autofilter list
Autofilter only displays 1000 rows.
More info he http://www.contextures.com/xlautofilter02.html#Limits -- Allllen "maryj" wrote: 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? -- maryj |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
values not appearing in autofilter list
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 maryj wrote: 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? -- maryj |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
values not appearing in autofilter list
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. maryj wrote: 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? -- maryj -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
values not appearing in autofilter list
Does anyone know if 2007 will allow more?
-- smither fan "Dave Peterson" wrote: 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. maryj wrote: 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? -- maryj -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto updating list | Excel Worksheet Functions | |||
matching one value in a list of values | Excel Worksheet Functions | |||
Keeping a cell value constant trhoughout a list of values | Excel Worksheet Functions | |||
create a list of single values from multiple values | Excel Worksheet Functions | |||
Data Validation - Dropdown List Not Appearing | Excel Discussion (Misc queries) |