INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help with Formulas and rolling up data...

Need help with Formulas and rolling up data...

(OP)
Hello, I am trying to build a workbook that will help me systemize my data roll-ups.
I am using excel to roll up some densities and volumes for weight.

To assist in the creation of rolling up individual items into one, I went ahead and made a column for "Group IDs" next to my data.

When I want to roll up specific lines of data on my spreadsheet into one line, I will put the same number next to those items. So when I go to roll up the data, I can use a formula to look up the Group ID field, and see what lines share the same number.

I can then take those lines and sum up the data. I plan on using formulas for all of this so while I put in my group Ids it will roll up the data at the same time.

I plan on rolling up the data on a different sheet.

My problem is I suck at using formulas, and I need some help.

I uploaded a picture to show you kind of what it Is I am working with.

The GRP ID column will have the numbers that define what line will be added with another.

How could I accomplish this with Formulas?





RE: Need help with Formulas and rolling up data...

(OP)
Well I guess I could, I just wanted the sheet to roll up data on the fly as I placed group IDs throughout the sheet so when I was done I didn't have to do any extra work to total sum my data, it's already done. I figured Formulas would be the way to go.

Could the filter be used in any such way?

RE: Need help with Formulas and rolling up data...

HI,

A picture is nice but pretty much worthless if you want help.

What we need is either to have your workbook uploaded or for you to post a cogent example of your source data and your summary table so we don't need to create from your pic.

I'm guessing that solutions might include a Pivot Table or formula aggregations via SUMPRODUCT() or SUMIFS().

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Need help with Formulas and rolling up data...

Lookup the "SumIf" function: =SumIf ( lookup range, lookup value, sum range ) this is perfect for your data.

RE: Need help with Formulas and rolling up data...

(OP)
Hey guys, thanks for your responses but I ended creating a macro to accomplish the roll up. I was hoping to avoid having to do so, but it ended working out for the best. The following code sums up data pretty quickly. You could sum up thousands of lines of data in a matter of seconds. There probably is a more efficient way of accomplishing this, but it probably isn't too much better considering this only takes seconds to sum your data.
I thought I'd post the code just incase anybody can find some use for it.

I used "Group IDs" (refer to my post above for more description on Group Ids) to identify the particular rows of data to sum. I assigned the group IDs to an array, that I later used as an index.

Here, if a group ID is in the row, then add the desired data in that row into another array(s) for later processing.

CODE

Set Data = Worksheets("NX Data")
Set Lastrow1 = Data.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    
lastrow2 = Lastrow1.row

Set RowRange = Data.Range("A2:A" & lastrow2)

    For Each c In RowRange 
    
        If IsEmpty(c) Then
            EmptyCheck = EmptyCheck + 1
            
            If EmptyCheck = Data.Range("A2:A" & lastrow2).Rows.count Then
                Worksheets(1).Protect                
Exit Sub End If Else ReDim Preserve GrpArray(i) GrpArray(i) = c.Value 'need to add data sheet reference to these range values!!! ReDim Preserve WeightSum(i) WeightSum(i) = Data.Cells(c.row, 13) ReDim Preserve VmomSum(i) VmomSum(i) = Data.Cells(c.row, 15) ReDim Preserve LmomSum(i) LmomSum(i) = Data.Cells(c.row, 17) ReDim Preserve TmomSum(i) TmomSum(i) = Data.Cells(c.row, 19) ReDim Preserve LCGMin(i) LCGMin(i) = Data.Cells(c.row, 22) ReDim Preserve LCGMax(i) LCGMax(i) = Data.Cells(c.row, 23) ReDim Preserve VCGMin(i) VCGMin(i) = Data.Cells(c.row, 24) ReDim Preserve VCGMax(i) VCGMax(i) = Data.Cells(c.row, 25) ReDim Preserve TCGMin(i) TCGMin(i) = Data.Cells(c.row, 26) ReDim Preserve TCGMax(i) TCGMax(i) = Data.Cells(c.row, 27) i = i + 1 End If Next

Next, assign group IDs onto a desired roll-up sheet so they can be sorted, and then scrubbed for duplicates. The result should be that the group IDs are in order, and there are no duplicates.

CODE

'assign group ids to roll up sheet
    count = 2
    For e = LBound(GrpArray) To UBound(GrpArray)
        If Not GrpArray(e) = "" Then
            Cells(count, 1).Value = GrpArray(e)
            count = count + 1
        End If
    Next e
  
  'used to sort and clean up GrpIDs
  Sub deleteDupes()
    Dim ws As Worksheet
    Dim j As Long
    Dim lr As Long
    Dim rngKey As Range
    Dim rngSort As Range
    
    Set ws = ActiveSheet
    With ws
        lr = .Range("a" & Rows.count).End(xlUp).row
        
        Set rngKey = .Range("a2:a" & lr)
        Set rngSort = .Range("a1:a" & lr) 'this would change if more columns were present
        With .Sort
            .SortFields.Clear
            .SortFields.Add rngKey
            .SetRange rngSort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        For j = lr To 3 Step -1 'heading in row 1
            If .Range("a" & j).Value = .Range("a" & j - 1).Value Then
                .Range("a" & j).EntireRow.Delete
            End If
        Next j
    End With
    Range("A1").Value = "GrpID"
    Range("A1").Font.Bold = True
End Sub 


Next we sum the data collected in the various arrays, based on the index of the array that collected the group IDs.

CODE

Set Lastrow1 = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    
lastrow2 = Lastrow1.row
  
  'Sum up Arrays
  For Each c In Range("A2:A" & lastrow2)
        Set WeightTotal = Cells(c.row, 4)
        Set VmomTotal = Cells(c.row, 6)
        Set LmomTotal = Cells(c.row, 8)
        Set TmomTotal = Cells(c.row, 10)
        Set LCGRngMin = Cells(c.row, 11)
        Set LCGRngMax = Cells(c.row, 12)
        Set VCGRngMin = Cells(c.row, 13)
        Set VCGRngMax = Cells(c.row, 14)
        Set TCGRngMin = Cells(c.row, 15)
        Set TCGRngMax = Cells(c.row, 16)
            'loop through GrpArray and Sum Arrays simultaneously
            'If GrpArray matches GrpId on sheet, assign weight value
            'to another array to be summed up
            '========================================================
            'Sum Weight
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = WeightSum(i)
                        ii = ii + 1
                    End If
            Next i
            WeightTotal = Application.WorksheetFunction.Sum(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'Sum Vmom
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = VmomSum(i)
                        ii = ii + 1
                    End If
            Next i
            VmomTotal = Application.WorksheetFunction.Sum(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'sum lmom
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = LmomSum(i)
                        ii = ii + 1
                    End If
            Next i
            LmomTotal = Application.WorksheetFunction.Sum(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'Tmom Sum
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = TmomSum(i)
                        ii = ii + 1
                    End If
            Next i
            TmomTotal = Application.WorksheetFunction.Sum(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'LCG Min
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = LCGMin(i)
                        ii = ii + 1
                    End If
            Next i
            LCGRngMin = Application.WorksheetFunction.Min(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'LCG max
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = LCGMax(i)
                        ii = ii + 1
                    End If
            Next i
            LCGRngMax = Application.WorksheetFunction.Max(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'VCG min
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = VCGMin(i)
                        ii = ii + 1
                    End If
            Next i
            VCGRngMin = Application.WorksheetFunction.Min(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'VCG Max
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = VCGMax(i)
                        ii = ii + 1
                    End If
            Next i
            VCGRngMax = Application.WorksheetFunction.Max(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'TCG Min
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = TCGMin(i)
                        ii = ii + 1
                    End If
            Next i
            TCGRngMin = Application.WorksheetFunction.Min(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
            'TCG Max
            For i = LBound(GrpArray) To UBound(GrpArray)
                    If GrpArray(i) = c.Value Then
                        ReDim Preserve SumCollection(ii)
                        SumCollection(ii) = TCGMax(i)
                        ii = ii + 1
                    End If
            Next i
            TCGRngMax = Application.WorksheetFunction.Max(SumCollection)
            ReDim SumCollection(ii)
            ii = 0
            
  Next 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close