Need help with Formulas and rolling up data...

Question

Need help with Formulas and rolling up data...

junfanbl (Marine/Ocean)

(OP)

22 Jan 16 13:25

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?

http://files.engineering.com/getfile.aspx?folder=405e8295-9d32-4d99-9787-9a

IRstuff (Aerospace) · Answer 1 · 2016-01-22 14:07:42

why not use the filter functionality?

TTFN
I can do absolutely anything. I'm an expert!
homework forum: //www.engineering.com/AskForum/aff/32.aspx
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers

junfanbl (Marine/Ocean) · Answer 2 · 2016-01-22 14:11:29

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?

SkipVought (Aerospace) · Answer 3 · 2016-01-22 18:34:46

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,
_{Just traded in my OLD subtlety...

for a NUance!}

JG2828 (Electrical) · Answer 4 · 2016-01-22 19:58:13

Not a true "roll up" but I would probably use a table with a slicer control on the Group ID column. Example attached.

http://files.engineering.com/getfile.aspx?folder=475a9385-74b3-40a7-bd0f-5e

IFRs (Petroleum) · Answer 5 · 2016-01-24 19:12:05

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

junfanbl (Marine/Ocean) · Answer 6 · 2016-02-15 11:51:45

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

Come Join Us!

Posting Guidelines

Need help with Formulas and rolling up data...