Nov 10 2022 11:35 AM
Hi all,
I have a spreadsheet where the filters don't seem to be working, making me think I am missing data that I'm not. I can find the data using Ctrl F just fine, but when I open the filter and search for a word, sometimes it doesn't appear to exist.
I have tried TRIM() and SUBSTITUTE() to remove any unwanted spaces and hidden characters, although when I apply CODE((MID)) to a particular cell that is having issues I get 32, which is a normal space character and shouldn't cause issues. I've also tried copying and pasting the data into a new sheet, and removing and re-applying the filter. Nothing seems to work and I'm baffled as to what else I can try. Any suggestions?
Nov 10 2022 11:43 AM - edited Nov 10 2022 11:46 AM
@geekybb wrote: ``Any suggestions?``
1. Show us some examples of data that you are searching for.
2. Show us how you set up the filter; that is, the conditions for the filter.
3. Show us how you set up the Find (ctrl-f); again, the options for the operation.
4. Provide an example Excel file (redacted) that demonstrates the problem.
Preferrably, attach the Excel file by clicking "browse" near the bottom of the reply window.
If the forum does not permit that (yet), upload the Excel file to a file-sharing website, and post a download URL that does not require that we log in.
I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum. But if you use onedrive, IMHO, be sure that the download URL does not allow others to edit the file. That will avoid accidental (and purposeful) changes by others. IMHO, do not use Google Drive. Many users confuse that with Google Sheets, which night alter the file.
If the forum does not permit you to post URLs (yet), type part of them manually. For example, the URL for this discussion is techcommunity dot microsoft dot com /t5/excel/filter-search-doesn-t-show-value-but-ctrl-f-finds-the-value/td-p/3673887
Nov 10 2022 01:17 PM
Hi @Joe User, thanks for the quick response. Here is a direct link to the csv file I downloaded, which I then saved as an Excel file. Per your other suggestions:
1. I have run into this issue multiple times, but the most recent one involves the city "West milford". It doesn't show up when I try to filter for it. Same problem with the zip code for this city. Funny thing is, if I first filter on NJ in the State field, then "West milford" shows up in the city filter.
2. There are no conditions for the filter. I'm just applying a filter to all my columns (from the Data pane) and using the dropdown arrow on the city column to open the filter options, and then using the search bar to find what I need. I went through the process of taking a screenshot, but I'm not able to upload images to my post. Seems weird.
3. Nothing fancy here, either. I'm literally just using the Ctrl + F shortcut on my keyboard to bring up the search bar.
Nov 10 2022 01:25 PM - edited Nov 10 2022 01:30 PM
Just a guess here without seeing the CSV (the link requires login). Does your data happen to have blank rows mixed in? It sounds like the filter may not be considering all rows so when the search is done via the filter, the item is not found. Something worth trying, clear the filter, select your entire range, apply filter, and try to locate the item through the filter's search.
Nov 10 2022 02:15 PM
Nov 10 2022 02:59 PM
A filter drop down list can display a maximum of 10,000 unique items - see Excel specifications and limits
Your City column contains 18,488 unique cities - almost double the maximum number the drop down list can show.
Nov 15 2022 11:57 AM
Nov 15 2022 12:51 PM
The dropdown list is incomplete if there are more than 10,000 unique values.
Filtering the incomplete dropdown list does not magically add items that weren't present.
At least, that's my guess. I have no inside knowledge of how this works.
Nov 15 2022 01:14 PM