Solved

How to combine rows while suming a single column in Excel

Posted on 2014-09-17
8
227 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

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!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

717 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