Extract chronological data

ansridhar · Apr 29th 2017

The sample file with requirements is enclosed. I have two sets of data i.e. BUY and SELL. Want to list data combining them in a chronological order. Tried using Index and Match combinations. Without luck. Had to manually cut and paste buy and sell data one below the other and then do it. Is there a way around directly?

**royUK** · Apr 30th 2017

Re: Extract chronological data

It would be easier to achieve what you want if the data was arranged in a table.

ansridhar · Apr 30th 2017

Re: Extract chronological data

Do you mean that the records of both buy and sell are stacked one below the other? The problem is the past data is already built up over past many months. Henceforth I can ensure that 'new data' are placed one below the other for easy use in future. Is there a solution for the current data which is available only in this format?

Logit · Apr 30th 2017

Re: Extract chronological data

.
.
Running the Macro Recorder will result in this:

Code

Option Explicit




Sub Macro1()
'Sort Tables
Application.ScreenUpdating = False
    Range("B2:G32").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=12
    Range("B34").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-18
    Range("H2:L32").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=42
    Range("B65").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-18
    Range("E34:G64").Select
    Application.CutCopyMode = False
    Range("E34:G64").Cut Destination:=Range("D34:F64")
    Range("D34:F64").Select
    ActiveWindow.SmallScroll Down:=6
    Range("C35:C95").Select
    ActiveWindow.SmallScroll Down:=-9
    Range("C35").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C35:C95") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B35:B95") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("B34:F95")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=42
    
    ActiveWindow.SmallScroll Down:=15
    Range("B66").Select
    ActiveWindow.SmallScroll Down:=-36
    Rows("34:85").Select
    Selection.Delete Shift:=xlUp
    Rows("43:43").Select
    Selection.Delete Shift:=xlUp
    Range("B34:F42").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll Down:=-12
Application.ScreenUpdating = True
End Sub

Display More

Usually not the most efficient means of coding but it gets the job done.

See attachment and click button.

ansridhar · May 1st 2017

Re: Extract chronological data

I got it. Thanks a lot. Will henceforth structure data in such a way that final reports are easily achieved by few commands rather than such a long code. Sincerely appreciating your effort to help me Roy, Logit.

**royUK** · May 1st 2017

Re: Extract chronological data

If the data is correctly entered and stored then you would probably be able to use a PivotTable, see the attached example. You can experiment with different layouts of the PivotTable

As always the recorded code is clumsy and much of it can be deleted or edited. The data range used should be made dynamic in the code and the selecting is unnecessary.

Logit · May 1st 2017

Re: Extract chronological data

Thank royUK. Excellent comparison.

ansridhar · May 2nd 2017

Re: Extract chronological data

Appreciate the fact. Thanks once again Roy.

**royUK** · May 2nd 2017

Re: Extract chronological data

Pleased to help

Extract chronological data

Participate now!

Share