Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Keep only the final subtotal line, including text

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a list which I have subtotaled. I now want to work exclusively with the subtotal lines. How do I delete the data and keep just the subtotal lines? I've tried copy, paste special values, sort and copy over the subotals, but there must be an easier way...AND I have another important issue that presents a challenge...

    In each of the lines there is data I want included in the new subtoal lines.

    I am subtotalling by an id number for each line. All of the items for each id number (ROW) will be the same except the dollar amounts. Like this...

    Smith ID2334 100 200 Act Blue
    Smith ID2334 300 600 Act Blue
    Jones ID1254 222 333 DED Green
    Jones ID1254 444 666 DED Green

    Now when I subtotal by ID # I get this:
    Smith ID2334 100 200 Act Blue
    Smith ID2334 300 600 Act Blue
    ID2334TOTAL 400 800 (BOLD)
    Jones ID1254 222 333 DED Green
    Jones ID1254 444 666 DED Green
    ID2334TOTAL 666 999 (BOLD)

    But what I want is:

    Smith ID2334 100 200 Act Blue
    Smith ID2334 300 600 Act Blue
    Smith ID2334TOTAL 400 800 Act Blue
    Jones ID1254 222 333 DED Green
    Jones ID1254 444 666 DED Green
    Jones ID1254TOTAL 666 999 Act Blue


    Then I just want those total lines left so I can do additional calculations with those line items.

    So I'm just left with a list of the subtotals that included the other data elements

    Like this:
    Smith ID2334TOTAL 400 800 Act Blue
    Jones ID1254TOTAL 666 999 Act Blue

    Excecpt for the fact it doesn't keep the other data elements, this is what the outline view looks like, but I don't know how to keep just that.

    I usually go in and copy down the data into the subtotal line..but I have 47,000 lines and not 20 hours to do this.


    Is this possible?



    Thanks!!!!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure it's easily possible...
    If you know VBA I'll send you a macro which you will need to edit.
    If not then mail me your sheet.
    excel_create@earthlink.net
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you do mail the sheet, please zip it up.
    I have a 28k connect...
    Thanks,
    Tom

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Looks like an ideal job for pivot tables
    :: Pharma Z - Family drugstore ::

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Give some details Chris...
    I don't know how to use pivot tables?
    Does the poster know how to use pivot tables?
    Tom

    DM, I'm off to get some shut-eye.
    Chris is more experienced with Excel than I.
    Maybe the pivot table solution is better.
    If you cannot find a solution, mail the sheet.
    Tom

    [ This Message was edited by: TsTom on 2002-05-11 05:41 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    well,

    assuming you have data like :

    Name Region Code Amount
    Mary North1 ABC1 21,000
    Jack South3 ABB7 18,500
    Mary North1 ABC1 50,000
    John North2 ABC1 10,000
    John North2 ABC1 30,000
    Mary North1 ABC1 15,000
    Mary North1 ABC1 21,000
    Jack South3 ABB7 18,500
    Mary North1 ABC1 50,000
    John North2 ABC1 10,000
    John North2 ABC1 30,000
    Mary North1 ABC1 15,000
    Mary North1 ABC1 21,000
    Jack South3 ABB7 18,500
    Mary North1 ABC1 50,000
    John North2 ABC1 10,000
    John North2 ABC1 30,000
    Mary North1 ABC1 15,000
    Jack South3 ABB7 45,000
    Jack North1 AAA3 50,000
    Jack North1 AAA3 50,000
    A pivot table can arrange the unique info in many different ways. One example would be as follows :

    Name Region Code Amount
    Mary North1 ABC1 258,000
    John North2 ABC2 130,000
    Jack South3 ABB7 100,000
    Jack North1 AAA3 100,000

    which is a quikcer way of subtotalling unique values and perhaps more manageable than a collapsed list of subtotals, which is why I'm suggesting it in this case.

    Explaining how to set them up is fiddly - the help files do this much better, and from memory I think I used the tutorial in excel which was very good.

    An indication as to their flexibility is that with a single drag of the mouse, moving "region" from the left to the top within the set up results in something like this :

    Name Code _North1__North2__South3
    Mary ABC1 258,000
    John ABC2 000,000 130,000
    Jack ABB7 000,000 000,000 100,000
    Jack AAA3 100,000

    (apologies for the 0s, it doesn't align very well, but thsi should give you the jist of it)

    Tom, your VBA suggestion is a good one : my knowledge of VBA is just about total zero, hence me posting something different at the same time as you posted

    Also potentially viable is data consolidation, or even advanced filter and copying unique values to perform some maths, maybe even DSUM formula too.... but off the top of my head, I'd suggest looking into pivots

    Hope this helps
    Chris

    edit : before someone jumps on my use of the word "quicker", as there's always *someone*, I mean in relation to copying paste special values as the poster is doing.....

    good VBA code, which I suspect is what Tom has, will do it in a second or two, which is even quicker than my quicker.

    [ This Message was edited by: Chris Davison on 2002-05-11 06:38 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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