We help IT Professionals succeed at work.

Advanced filtering on Excel, wildcard issue.


I have attached an excel file with an example of advanced filter setup. The problem i am having is getting the widlcard filters to work so that lines with account number 863XX will list with values above a certain value, but also have the subtotals list in the filter results if the value is above a certain value.

In the example shown (attached) i can't seem to get the lines listed as 86300 in the results, only the subtotals. I'm looking to get both to show in the filter results (starting at M10)

I'm really hoping someone can tell me where i am going wrong.

Watch Question

Is this what your looking for ?
these are the changes I made
1) Copied the whole header fields to M:U
2) When you ask for Advancedfilter you should first click on A1 then select advanced filter the list in place should be your data ie A1:I35 and not as you had  it before M1:O1
3) the criteria should be M2:U4
4) Most importantly when you request Advanced filter you should clik on copy to an other location and I selected M10:H600 to allow max lines.

Pls chk if this is what you want.


Hi Gowflow

That is not what i want, i want to be able to specify 863XX so i can get a86300, 86301, 86304 etc if above a certain value, and the subtotal if above a certain value.

ah okis this better? By the way in your example you dont hv any value bigger than 86300 they are all smaller.
Anyway try this one
The real problem, you have is in formating. The cell's A2 to A5 all have numbers. Except A6 has text (becouse word Total is added). The filter you have is with wildcards, but those only aply to text. You can solve this two ways. One is, you put an apostrophe (') in front of your values in cells A2 to A5 and your filter will recognise them all. I've done that for you in the attached file.

Otherwise, you have to work with number filters and operators, which leaves you with >,<,=>,=< to use.You cannot use ??? here. That is a solution offered by gowflow.

Good luck!


Thanks guys.

Gowflow, your solution doesn't seem to work because using <836?? returns other values like 72310 and other account numbers not shown in the example.

GasperK, your solution works with the static example, but I am still struggling to get it working on my real data because cells in the A column reference another sheet so are in the format of =OtherSheet!A2 etc

It works by adding a ' in front of the static example, but how do i modify the contents of the cell to make it work when the data is in another sheet?

I've attached a solution to your problem...
Its ok you used the >= or <= that I suggested (the asker did not make it clear in his post that he needed all values in an interval !!) this would be more appropriate as it would include the total as well !!
The filter for totals was written correctly by broadcastwarehouse. Your version is wrong(!). It's pure luck that it actually works because you are using arithmetic operators (<,>,=...) to filter fields containing text (123123 total)... So my solution stands. The only thing wrong with it is that i accidentally wrote 863999 in cell L2 where what I should have written is 86399... Sorry.