Subtotals/related records (Excell 2000/SR-1)

Topic

New Reply

I have a labor export from another program going to excel. Say I have 1000 records. I have 178 records from Widget 1, 209 from Widget 2, and so on. I know that I can subtotal them and just view the subtotals by “Hiding Detail”.

I want to eliminate the other records and have just the subtotal records left in the workbook. Is there a way to do this?

Reply | Quote

Replies

I’m not if by eliminate the other records you mean permanently or not, but the Auto Filter should let you do it either way.

Place the cursor in the data and go to Data, Filter and check the AutoFilter item. You should now have a set of drop down buttons in each column header. You can use those to select the items you want to view, and when that is done use the AutoSum button to enter the totals. You will notice that instead of the usual Sum(Range), it inserts SUBTOTAL(9,Range). The 9 indicates that addition will be used for the subtotal but you can alter to say 3 if you want a count. You can subtotal all columns or just as many as you want.

If you want to discard the rest of the data, you can just copy the filtered list to a new sheet and delete the old sheet when you are happy you have all you need.

Andrew C

Reply | Quote

Thanks for the reply! I’ve attached the file for ease of discussion.

As you can see the sheet is already subtotaled. When I use the “Autofilter” I can choose the cells that contain the word “Total” (or most any choice that would get me a total (sub-total) record), the totals in the other colums disappear. I need the entire subtotal record!

Reply | Quote

Hope I understand you now. If you hide detail and then hightlight the remaining data, and Goto, Special and select Visible Cells only, then copy and paste to a new sheet and all you should have is the subtotal rows. I am returning the workbook with thatoperation carried out and the results are on sheet 2. I have attached a toolbar with two buttons that should make it easier. The first buttons selects the current area (you should have the cursor within the cells you want to copy) and the second selects only visible cells.

Hope it helps.

Andrew C

Reply | Quote

Dat did it. Thanks for your help!!!

Reply | Quote