Advanced Filter Trouble

[mdshields]

Hello,

I've found that an advanced filter is likely going to be my easiest option to filter multiple criteria and display the results.

The Problem: The filter currently will not show results from data entered by a new user (me). I took over the sheet at row 3166 Data Sheet (some values after I took over still show up because they were created by using the drag feature).

I have tried the following in hopes that it was a formatting discrepancy: clear all formatting from data, copy paste into new workbook as value, redo the filter multiple times, change the range, make the data a table. I have tried filtering by only the range of values I know to be the ones I myself entered and the filter still returns no results as if those values don't exists. When using an auto filter the data is visible so I'm not sure why it won't appear in my advanced filter search.

As a test in the search sheet in A10 enter PA00000113 and hit filter data. Now try and do CA00000112. You should find the CA number doesn't show up yet it exists in the Data sheet.

Here's a link to my file: https://www.dropbox.com/s/wlued1kq21zw9ki/PA%20Data%20Base%20Advanced%20Filter.xlsm?dl=0

I had posted this problem earlier but didn't include the file and worded it poorly. If a mod runs across this feel free to delete my previous post.

Thanks for taking a look, you guys are far beyond my excel knowledge.

 

[starl]

1. If you want the moderators to delete your original posts, IF there are no responses, you should report it and request it be removed.
2. Note that many users will not download a workbook - either can't or won't. So don't rely on a workbook to ask your question for you
3. Why do you have a * in your empty fields in your query table? If you want it to return ANY matches for that field - then you leave the field blank. The asterisk is used to replace unknown characters. Like *burger will return hamburger or cheeseburger.

 

[wideboydixon]

I think I know why this is happening to you. If you put:

CA00000112 in PACA
4.76 in DIAMETER/SIZE
302.2 in GAGE LGTH
25 in EXTENSION LENGTH

And then hit Filter Data, your record will be found. The problem is that you're using "*" as a wildcard but it doesn't work on numbers; only text. Go to your data sheet and look at cell C2. This is not a number; it's text. This means that your wildcard will work as you intended on that record. Same for K2. However, if you go to cell C3249 then you'll see this is a number and not text. Same for K3249 and L3249. Here's what to do:

On the Data sheet, select the whole of column C. On the Data ribbon, click Text to Columns and click through the first two steps. One the third step, click the Text option under Column data format and then click Finish. Do the same for columns K and L. Now re-try your search and it should work.

Bazinga.

WBD

 

[mdshields]

It worked! Thank you so much, I would never have thought to do that. Also, do you know how I might be able to search for say 50 in C10 on the Search sheet and find results with a range of +/- 1? Or search for .79 in F10 on the same sheet and get all values with .79 RAD.

Thanks again for helping me with that, I was very lost and becoming pretty frustrated.

 

[starl]

You can use formulas with Advanced Filter, but you need to set them up in a separate area.

1. put the formula in an area to the right of your header query
2. Do not use a header for the formula
3. put the formula in the row below the headers (same row where you have the criterion for the headers)
4. Use relative referencing and refer to the FIRST row of DATA. So, if your data starts on row 2, you'd want a formula such as: = AND(C2>=49,C2=<51)
This will find values in column C that are between 49 and 50 (including those numbers).

 

[wideboydixon]

You've just converted your numbers to text so you can't now treat them as numbers. If you want to do that then you're going to need to find an alternative method of applying the filter.

WBD