?
Solved

How to combine rows while suming a single column in Excel

Posted on 2014-09-17
8
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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 2000 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

771 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