Excel: Consolidating multiple lines (Subtotaled) 1

[Robertislearning]

Hi everyone! I have a weekly report of 1,000+ lines that is provided to me to "condense". The report looks as follows:
A B C D E
Prob# CustName Date Time Duration (minutes)
001 Eat at Joe's 12/01 10:00a 10
001 Eat at Joe's 12/01 11:00a 50
001 Eat at Joe's 12/01 12:00a 20
001 Eat at Joe's 12/01 12:10a 30
001 Total 110

There are hundreds of these sections for each customer that are subtotaled. For example, the cell that contains the total above(110)has a formula like this: =SUBTOTAL(9,E1:E4)

Surely there has to be a way to automate this. My poor, feeble brain cannot handle such complex tasks! (guess that's why they asked me to 'condense' the report!)

What I'm looking for is a macro that will sum up each line item and then place that sum in the first line of each customer section. Making the above sample look like this one below.

A B C D E
Prob# CustName Date Time Duration (minutes)
001 Eat at Joe's 12/01 10:00a 110
001 Total 110

Thank you in advance for any advice & help!!!!

Robert.

 

[AnotherHiggins]

I'm not clear - are the subtotals already there, or are you trying to create/recreate them?

I think you need to have a look at Data > Subtotals.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[Robertislearning]

Hi John,

The Subtotals are already there when the spreadsheet is sent to me. My 'to do' is to consolidate each section (like the Eat at Joe's section above).

For example, in the sample given in my first post, I go through and highlight and sum cells E2 (value = 10) through E5 (value = 30). I then place this sum (110) in cell E2 and delete lines 3, 4 & 5.

Note: Looks like the formatting didn't keep, there are five columns (A - E).

Thanks!

 

[AnotherHiggins]

First, a test to see how it was done before it was sent to you. At the far left (to the left of the row numbers), are there little boxes containing 1, 2 and 3? If so, click on 2.

If those aren't there, then try this:

Select a column where there is no subtotal info (Maybe column C, from the look of your example). Click on the C at the top of the column to select the entire column.

Now go to Edit > Goto > Special > Blanks. This should select all of the rows containing subtotal information. Right click on one of the selected cells and choose Delete > Entire Row.

Voila! No more subtotals.

Now, go to Data > Subtotals. For the "At each change in" box, select Prob#. For "Use function", select SUM. For "Add Subtotal to:", check the box beside Duration (minutes).

At the far left (to the left of the row numbers), there will be little boxes containing 1, 2 and 3. Click on 2.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[Robertislearning]

Thank you very much for your assistance!!!! You have helped tremendously. I apologize for the delay in responding, I've been tasked with a project that took priority over my normal duties (I'm sure you all can relate....)

Again, Thank you very much!!!!!!

 

[AnotherHiggins]

Glad you got it sorted out!


[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.