Keep only the final subtotal line, including text

dmdorado · May 11th, 2002, 06:20 AM

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

Tom Schreiner · May 11th, 2002, 06:27 AM

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

Tom Schreiner · May 11th, 2002, 06:29 AM

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

Chris Davison · May 11th, 2002, 06:32 AM

Looks like an ideal job for pivot tables

Tom Schreiner · May 11th, 2002, 06:35 AM

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 ]

Chris Davison · May 11th, 2002, 07:28 AM

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 ]

May 11th, 2002, 06:32 AM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Looks like an ideal job for pivot tables __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 11th, 2002, 06:20 AM	#1
dmdorado New Member Join Date: May 2002 Posts: 2	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!!!!

May 11th, 2002, 06:27 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

May 11th, 2002, 06:29 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	If you do mail the sheet, please zip it up. I have a 28k connect... Thanks, Tom

May 11th, 2002, 06:35 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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 ]

May 11th, 2002, 07:28 AM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ]