Filter search doesn't show value, but Ctrl F finds the value

Copper Contributor

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?

8 Replies

@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

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.

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.

Apologies for the link. I've just uploaded the file to Dropbox: https://www.dropbox.com/s/lrbmo84tg6zt4lx/ZIP-COUNTY-Master.xlsx?dl=0

As far as your suggestion, my dataset doesn't have any blank rows.

@geekybb 

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.

But if I'm using the search bar in the filter, shouldn't that not matter? For example, I'll search for "milford" and it returns 3 cities in the dropdown list: "Milford", "New milford", and 'South milford". It's just "West milford" that doesn't show.

@geekybb 

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.

Hmm, that could be it. But I've used filtering on very large data sets in the past (much larger than this one) and never ran into this issue. For now, I'm switching to Google sheets because I'm not running into any problems there.