Excel Subtotal with more info

[tav1035]

How can I add more columns to a subtotal function for info only.
I subtotal the total hours in coulmn "L" at each change in PM#'s in column "B"
Then I copy the subtotal and paste it to another spreadsheet.

The subtotal info is only the PM# and the TOTAL HOURS.
I would like to capture the PM DESCRIPTION from column "C" and a few other info that doesn't change within its group of subtotals.

I currently have this subtotal function automated after a query refresh from the database.

PM# PM DESC ROUTE ASSET JOBPLAN# QTY HOURS TT HRS
46630 RTE Cooling Tower#1 2643 21762 13547 1 3 3
46630 RTE Cooling Tower#1 2643 21762 13548 4 2 8
46630 RTE Cooling Tower#1 2643 21762 13549 1 2 2
46630 RTE Cooling Tower#1 2643 21762 13550 2 2 4
46630 RTE Cooling Tower#1 2643 21762 13551 1 2 2
46630 RTE Cooling Tower#1 2643 21762 13552 1 2 2
46630 Total 21

So what's the easiest and fastest way to get the data copied?
Is it to copy the last row before the subtotal Columns "C:K" and paste down in the subtotal area, then copy/paste it over. How can I automate that?

What about adding a formula to the total hours column that would look at the column "B" in the row that the formula resides and sums all like PM#'s then I could just copy and paste the 1 row from each group? I don't know it seems simple but I am out of ideas.
This is a huge automated workbook that forecast PM's for the next 2 years and provides analysis on level loading. It has been in use for 5 years, (about 5,000 rows of data, and I'm trying to remove most of the manual munipulation from it.
Thanks
Tommy

 

[SkipVought]

Hi,

If you do not want to open the whole table, then use the PivotTable Wizard to select only the fields you want to include on your report.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

You can get multiple subtotals in the same worksheet. If for example I have a list of cars for sale with their partculars in columns. The fields being Make, Model, Color, Doors, Interior to name a few.

Then if I sort the wroksheet by Make, then by Model, then by color (for example)

1. I can get multiple subtotals by creating the first subtotal for the Make;

2. then placing the cursor withing the same table, I can go to Subtotals (this time uncheck "Replace current subtotals")and create a second level of subtotals for the model and then

3. Repeat step 2 for Color.

You can add as many subtotals as you desire and each time it adds a level to the Grouping.

Tried and Tested.



Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[Gavona]

Having created the subtotals. You can put a formula in the Desc column to bring down the Desc from the row above.

To do this:
Select the Desc column within your data
Edit, Goto, Special Blanks
=[uparrow] Ctrl-Enter

Then to avoid bloating the spreadsheet with all those formulae
Select the Desc column again
Copy, PasteSpecial, Values

Gavin