Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Ohio, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Ohio, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Ohio, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Dat did it. Thanks for your help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •