Keep only the final subtotal line, including text

dmdorado

New Member
Joined
May 10, 2002
Messages
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!!!!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
If you do mail the sheet, please zip it up.
I have a 28k connect...
Thanks,
Tom
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top