Problems Filtering in Excel

Topic

New Reply

Hi.

I’m having problems filtering my data in Excel. I’ve attached the sheet that I would like to filter. Basically I want to add up the value in the “B” column if the “A” column (Account Number) matches. The results would then be put into a new sheet “Summary”. The total number of lines in the “detail” sheet is variable. The “Summary” sheet will have a line for each account number and its total.
please help.

Thank you very much,
Mark

Reply | Quote

Replies

Hi Mark,

You can use autofilter to help achieve what you want. Select a cell in your range, goto Data, Filter and select Autofilter. You should see dropdown buttons appear on cells A1 to C1.

Goto to first blank row (602 in the file you posted) and enter the following formula in B : =SUBTOTAL(9,B2:B600).

If you now click on the dropdown button in A1, select the account number you want summarised you should get a list of each entry for that number and a subtotal. You can then copy that data to a new sheet if required, but perhaps there is no need to do that.

If you goto Tools, Customize, and select the Commands tab, then in the categories section select Data, and in the commands box, scroll down until you find an icon that says Autifilter. Drag that to your toolbar. You might as well also select one titled Show All (no image). If you now select a cell in A, and click the autofilter button it should filter just the records for that account number. To restore the full list, just click Show All.

Hope that helps you.

Andrew C

Reply | Quote

Hi Andrew.

Thank you for your time and help.

I should of also mentioned that I will need this is a macro. Basically what I’m looking at right now is the Range(“??”).autofilter feature and seeing if that works. My macro creates the heading on the second sheet “Summary”, but the summarization of the account numbers and their totals is not complete and that is where I’m stuck. The AutoFilter feature is great, but I need it to filter each account number, sum the total after the filter and paste it into the new sheet “Summary” with the account number and total listed in one line. I hope I haven’t confused the issue. Basically, what you see in the drop down of the account number in the Auto Filter feature is what I need on “Summary” Sheet with the totals.

By the way, I am a novice to Excel, so sometimes I may not understand completely.

Thank you for your patience.
Mark

Reply | Quote

Hi Mark, I am attaching a copy of your workbook with an additional sheet with a pivot table summarising subtotals for each A/c No.

Is that more or less what you as content for the second sheet, and is a Pivot table ok, or do still want a macro ?

Andrew

Reply | Quote

The other nice thing about a pivot table is that if you enable ‘drill down’ you can double click on any cell and it wil generate a list of the data elements that gave rise to that total…

Reply | Quote