Advanced filtering on Excel, wildcard issue.

Hi.

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.

Thanks
advanced-filter-test-2.xlsx
broadcastwarehouseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
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.
Rgds/fowflow
advanced-filter-test-2.xlsx
0
broadcastwarehouseAuthor Commented:
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.

Regards
0
gowflowCommented:
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
gowflow
advanced-filter-test-2.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gašper KamenšekExcel MVPCommented:
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!
advanced-filter-test-2-1.xlsx
0
broadcastwarehouseAuthor Commented:
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?

Regards
Scott
0
Gašper KamenšekExcel MVPCommented:
I've attached a solution to your problem...
solution.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
Gasperk
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 !!
gowflow
solution.xlsx
0
Gašper KamenšekExcel MVPCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.