Solved

How to combine rows while suming a single column in Excel

Posted on 2014-09-17
8
222 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now