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?
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...
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
RE: Need help with Formulas and rolling up data...
Could the filter be used in any such way?
RE: Need help with Formulas and rolling up data...
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!
RE: Need help with Formulas and rolling up data...
RE: Need help with Formulas and rolling up data...
RE: Need help with Formulas and rolling up 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 NextNext, 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 SubNext 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