View Full Version : Need Help on Data Slicing

2011-08-09, 08:48 PM
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.

2011-08-09, 09:06 PM
Using EXCEL 2007 version

Bob Phillips
2011-08-09, 10:40 PM
Try this

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

2011-08-09, 10:49 PM
Thanks...Working. Appreciate your help.