Excel/Data, Filter, Auto Filter
Often I set up spreadsheets and use the above tools. But one problem I have is when I first do a subtotal in one column and I might end up with a couple hundred separate subtotals. I want to filter with the custom option for the subtotal, but this tool ignores the subtotals and just looks at the entered data. Is there another way. Maybe my words don't explain, so I'll set up a simple example.
ABC 1/12/13 30.00
ABC 2/13/13 40.00
ABC 2/26/13 16.00
BCD 2/13/13 60.00
BCD 2/28/13 25.00
CDE 1/06/13 20.00
CDE 1/29/13 25.00
CDE 2/15/13 50.00
CDE 2/17/13 25.00
Okay, I've already done some sorting by column A amd B. Now I do a subtotal of column C, and I'll get 86.00, 85.00 and 120.00.
I would like to auto filter - custom those subtotals say with anything equal to or greater than 100.00, and I would only get the one for 120.00. But there doesn't seem to be a way to do that. Is there?
ANSWER: Art -
I would like to come at this question from what the end result is to look like. Would you put together a quick "screen" of that for me, like you did for your example?
Thank you - Andrea
---------- FOLLOW-UP ----------
QUESTION: Okay. I can't scan my excel spreadsheet so I did a copy/paste. Following from the original data in my question, I then did a Data/Subtotals and at each change in name use function Sum, Add subtotals under amt. Then I get the layout below that I call Problem Set Up. Look below it for what I want to do next.
PROBLEM SET UP
name date amt
ABC 01/12/13 30.00
ABC 02/13/13 40.00
ABC 02/26/13 16.00
ABC Total 86.00
BCD 02/13/13 60.00
BCD 02/28/13 25.00
BCD Total 85.00
CDE 01/06/13 20.00
CDE 01/29/13 25.00
CDE 02/15/13 50.00
CDE 02/17/13 25.00
CDE Total 120.00
Grand Total 291.00
Now what I tried to do is Data, Filter, Autofilter and go under the selection box in the amt column and choose Custom. Then the new selection criteria comes up and says "Show rows where amt..."
then you choose the phrase "is greater than" in first box and "100.00" in second box and hit Okay. Below is what I would like to see, but what comes up is 0.00, because the Excel tool looks for the entered data and not for the calculated subtotal data.
name date amt
CDE Total 120.00
Here is one answer to your request. I am sure there are other answers that can be done with macros or VBA; I went for the most direct and easiest to manipulate.
The problem was filtering out only those companies with a total over 100. The problems with this are that the Grand Total is also over 100, and you don't want that; and that when you use the Subtotal the subtotals created aren't actual numbers - they're formulas. So they don't show up in a filter.
I've solved this problem two ways: I am using Autofilter but through a macro, so I have many more options. Also within the macro I can copy the table and PasteSpecial, converting the subtotals into hard numbers. Then the filter works.
Here is the macro, which I based on the small table you provided:
.AutoFilterMode = False
.Range("A1:C15").AutoFilter Field:=1, Criteria1:="<>Grand Total"
.Range("A1:C15").AutoFilter Field:=3, Criteria1:=">100"
Notice that the PasteSpecial pastes over the original table, which means the Subtotal function won't work. If you want to be able to modify the table and get new results, have the PasteSpecial Range be somewhere else. You'll have two tables, but everytime you run the macro it will be updated.
The macro filters out the Grand Total line, but any other lines meeting the criteria for column 3, AMT, will show up.
Copy and paste this code in the sheet that contains the table (just right-click the sheet tab and select "View Code"), customize it and you're ready to go.
Let me know if you have problems - Andrea