Need Help on Data Slicing

komarag

New member
Joined
Aug 9, 2011
Messages
26
Reaction score
0
Points
0
I have following input data in this format.

TypePeriod Start DateQTYNameCommentActual06/01/20111.00XYZActivity 1Actual06/10/20113.00XYZActivity 2Actual06/30/20118.00XYZActivity 1Cutoff07/03/2011XYZCut off date for XYZActual07/10/201140.00XYZActivity 1Actual07/10/20115.00XYZActivity 2Actual07/17/20111.50XYZActivity 1Actual07/17/201123.00XYZActivity 2Actual07/17/20112.00ABCActivity 1Actual07/17/201131.50ABCActivity 2Cutoff07/20/2011ABCCut off date for ABCActual07/24/201115.00ABCActivity 1Actual07/24/20110.50ABCActivity 2Actual07/24/201139.50ABCActivity 1Actual07/24/20114.00ABCActivity 2Actual08/01/20114.50ABCActivity 1Actual08/01/20112.50ABCActivity 2
I want to Slice the data based on the "Cut off" Record date, sum the Quantity before and after the "Cut off Record date".

For example, the output should look like this...

NameCutoff DateBefore cut off date totalsAfter cut off totalsXYZ7/3/20111269.5ABC7/20/201133.566


Added the Input and output formats in the attachment.
Appreciate your help.
 

Attachments

  • EXCEL HELP.xlsx
    8.6 KB · Views: 14

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Try this

Code:
Public Sub ProcessData()
Dim ws As Worksheet
Dim Lastrow As Long
Dim Nextrow As Long
Dim i As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        Set ws = Worksheets.Add
        ws.Range("A1:D1").Value = Array("Name", "Cutoff Date", "Before cut off date totals", "After cut off totals")
        ws.Range("A1:D1").ColumnWidth = 10
        ws.Range("A1:D1").WrapText = True
        Nextrow = 2
    
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To Lastrow
        
            If .Cells(i, "A").Value2 = "Cutoff" Then
            
                ws.Cells(Nextrow, "A").Value2 = .Cells(i, "D").Value2
                ws.Cells(Nextrow, "B").Value2 = .Cells(i, "B").Text
                ws.Cells(Nextrow, "C").Value2 = Application.SumIfs(.Columns("C"), .Columns("D"), .Cells(i, "D").Value2, _
                                                                                  .Columns("B"), "<=" & .Cells(i, "B").Text)
                ws.Cells(Nextrow, "D").Value2 = Application.SumIfs(.Columns("C"), .Columns("D"), .Cells(i, "D").Value2, _
                                                                                  .Columns("B"), ">" & .Cells(i, "B").Text)
                Nextrow = Nextrow + 1
            End If
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub
 
Top