Solved

How to combine rows while suming a single column in Excel

Posted on 2014-09-17
8
225 Views
Last Modified: 2014-09-17
I'm trying to take the following attached table and combine all instances of a single ticket into a single row with the 'total_seconds' column sum'ed together.

Nearly the same thing was done in http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26311764.html but I was not able to reverse engineer the VB Script. Perhaps someone can help get that script working for something like this document.

Script:
Dim i As Integer
i = Range("A1").End(xlDown).Row

Application.ScreenUpdating = False

    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Quantity"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((RC[-3]=R2C1:R" & i & "C1)*(RC[-2]=R2C2:R" & i & "C2),R2C3:R" & i & "C3)"
    Range("D2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C2").Select
    Application.CutCopyMode = False
    Selection.EntireColumn.Delete
    Range("A1").Select
    Range("A1:C" & i).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "D1"), Unique:=True
    Range("A1:C1").Select
    Selection.EntireColumn.Delete
    Range("A1").Select

Application.ScreenUpdating = True

End Sub

Open in new window

Report-2014-09-01-to-2014-09-16.xlsx
0
Comment
Question by:bc it
  • 4
  • 4
8 Comments
 

Author Comment

by:bc it
ID: 40329331
Found another, clearer example here http://www.experts-exchange.com/Software/Microsoft_Applications/Q_23065563.html

But the Scripting.Dictionary class does not exist for my Mac 2011 version of Excel......
0
 
LVL 4

Expert Comment

by:rlarian
ID: 40329332
why not just use a pivot table?
Row Labels      Sum of total_seconds
747993      69195
754371      58646
756493      22600
757159      607
758087      3756
758234      7287
758273      893
758328      71
758615      7
758618      6
758627      170
758649      46
758887      4671
758988      2
759217      506
759275      3935
0
 

Author Comment

by:bc it
ID: 40329343
@rlarian, I've been trying and I've been able to get that far, but am not sure how I'd set up the pivot table to display all the other columns along with those totals. Know how'd that would be set up?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:rlarian
ID: 40329351
Ticket Created and ticket updated would not make sense to include, but do you want the rest of the columns in the final result?
0
 

Author Comment

by:bc it
ID: 40329354
Exactly, so I would end up with a list of ticket numbers along with all of their metadata, including the sum'ed total_seconds
0
 
LVL 4

Accepted Solution

by:
rlarian earned 500 total points
ID: 40329376
take a look at the attached. sometime the quick and dirty will get the job done - pivot table and vlookup. the pivot 2 sheet shows a multi tiered table that might give you some ideas.
0
 
LVL 4

Expert Comment

by:rlarian
ID: 40329377
0
 

Author Comment

by:bc it
ID: 40329392
Very cool, thanks! I'll use this to make it happen!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question