Results 1 to 4 of 4

Thread: Need Help on Data Slicing

  1. #1

    Need Help on Data Slicing

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Using EXCEL 2007 version

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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

  4. #4
    Thanks...Working. Appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts