Automatically merge two irregular time series in Excel

Question

Here's the situation:

Data is provided to me in two source data series.
The two source data series both have the same columns, one of which contains a date.
Each row is unique, although there could be multiple rows with the same date value.
I need to create a merged data series containing all of the rows from both source data series sorted by date.
The source series need to be retained in its own columns in the spreadsheet, so we can't simply copy and paste both series into a single series each time and sort.
We need some solution that automatically generates the merged data series once the data from the source series has been input.

The closest questions I've found on here all seem to rely on R, but is there any way to do this in Excel?

Edit: Here's some simplified sample data (the actual data has a bunch more columns) and the desired result:

Data source 1:

Type    Date       Num  Name     Split        Amount
Deposit 01/01/2014 wire John Doe Town Bank     $1234.56
Deposit 01/06/2014 wire Jane Doe Village Bank  $3434.65
Check   02/24/2014 clr  Jim Doe  City Bank    -$4000.00

Data source 2:

Type    Date       Num  Name     Split        Amount
Deposit 12/11/2013 45   John Doe Town Bank      -$25.56
Deposit 12/25/2013 wire Jane Doe Village Bank   $334.65
Deposit 01/11/2014 45   John Doe Town Bank      -$25.56
Deposit 01/14/2014 wire Jane Doe Village Bank   $108.00
Deposit 01/27/2014 wire Jane Doe Village Bank   $314.15
Deposit 02/11/2014 45   John Doe Town Bank      -$25.56
Deposit 02/13/2014 wire Jane Doe Village Bank     $2.15

Desired result:

Type    Date       Num  Name     Split        Amount
Deposit 12/11/2013 45   John Doe Town Bank      -$25.56
Deposit 12/25/2013 wire Jane Doe Village Bank   $334.65
Deposit 01/01/2014 wire John Doe Town Bank     $1234.56
Deposit 01/06/2014 wire Jane Doe Village Bank  $3434.65
Deposit 01/11/2014 45   John Doe Town Bank      -$25.56
Deposit 01/14/2014 wire Jane Doe Village Bank   $108.00
Deposit 01/27/2014 wire Jane Doe Village Bank   $314.15
Deposit 02/11/2014 45   John Doe Town Bank      -$25.56
Deposit 02/13/2014 wire Jane Doe Village Bank     $2.15
Check   02/24/2014 clr  Jim Doe  City Bank    -$4000.00

It's not clear what you mean by "the source series need to be retained separately" — Tim Williams, Mar 30 '14 at 04:03
At the very least provide a sample data set - else it is likely your question will be closed. — brettdj, Mar 30 '14 at 06:50
@TimWilliams The two source series need to be included in the spreadsheet as we received them. — Vincent, Apr 01 '14 at 12:28
Sorry, but "included in the spreadsheet as we received them" is still unclear. What exactly is stopping you from copy/pasting both lists together and then sorting the joined list? Do you mean that as new rows are added to either source the combined list needs to be automatically updated with the new row? — Tim Williams, Apr 01 '14 at 15:56
@TimWilliams The requirements for the project are to make the only manual step copying and pasting in the source data to the appropriate cells in order to reduce the likelihood of error. There are hundreds of these pairs of data sources and the data is updated several times a month, so two steps (copy and paste data source 1, copy and paste date source 2) is not optimal but it's better than five (those two + copy and paste source 1 into merged list, copy and paste source 2 into merged list, sort). (Obviously this screams for a programatic solution but we are unable to get one anytime soon.) — Vincent, Apr 02 '14 at 13:40

score 1 · Accepted Answer · answered Apr 02 '14 at 18:42

Something like this should work:

Sub Tester()

    Dim rng1 As Range, rng2 As Range, cr As Range

    Set rng1 = Worksheets("Sheet1").Range("A1").CurrentRegion
    Set rng1 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1) 'omit headers

    Set rng2 = Worksheets("Sheet2").Range("A1").CurrentRegion
    Set rng2 = rng2.Offset(1, 0).Resize(rng2.Rows.Count - 1) 'omit headers

    With Worksheets("Summary")

        Set cr = .Range("A1").CurrentRegion

        cr.Offset(1, 0).ClearContents 'clear existing data
        rng1.Copy .Range("A2")
        rng2.Copy .Range("A2").Offset(rng1.Rows.Count)

        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=cr.Columns(2), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
            .SetRange cr
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With

    End With
End Sub

Automatically merge two irregular time series in Excel

1 Answers1