matlab - 合并具有连接日期的行

Question

我有一个包含客户和订阅数据的大型 Excel 表。从此表中，我想将记录/行与连接 stop_ 和 start_dates 合并，并在新工作表中显示结果。数据的简化版本如下所示。

Customer_id subscription_id   start_date    stop_date
1034        RV4               30-4-2012     30-1-2015
1035        AB7               30-1-2014     30-3-2014
1035        AB6               30-1-2014     30-3-2014
1035        AB7               30-12-2013    30-1-2014
1035        AB7               12-12-2012    30-12-2013
1035        AB7               12-9-2010     14-1-2011

因此，公式必须检查 customer_id 和 subscription_id。当工作表中的两行或多行匹配且其中一行的 stop_date 与另一行的 start_date 重叠时，则在提取和合并后，必须显示一个新行与第一行的 start_date 和另一行的 stop_date。如果有多行具有连接日期，这也必须工作。提取后所有不符合这些条件的行都保持不变。所以结果会是这样的:

Customer_id subscription_id start_date  stop_date
1034        RV4             30-4-2012   30-1-2015
1035        AB6             30-1-2014   30-3-2014
1035        AB7             12-12-2012  30-3-2014
1035        AB7             12-9-2010   14-1-2011

在将新数据添加到原始工作表中时，动态解决方案将是理想的。虽然我知道当您确定要查找的行始终位于彼此下方时这是可能的，但这里并非如此，它不会为您提供非常动态的解决方案。

所以我猜在Excel中需要某种数组函数，但经过大量搜索后我找不到合适的解决方案。我也有 MATLAB 可用，但不知道在该程序中从哪里开始遇到这样的问题。

Answer 1

动态解决方案可能是可能的，但如果数据集很大，它可能会使事情陷入困境，因为每次更改单元格时都需要运行它。

基本上我能看到的最好的方法是从你的 customer_id 和 subscription_id 创建唯一的键，然后收集该键下的所有日期范围并将它们合并。

这样的事情应该可以帮助您入门(需要引用 Microsoft Scripting Runtime):

Public Sub LinkSubscriptionDates()

    Dim data As Dictionary, source As Worksheet, target As Worksheet

    Set source = ActiveSheet
    Set data = GetSubscriptions(source)
    Set target = source.Parent.Worksheets.Add

    'Copy headers
    target.Range(target.Cells(1, 1), target.Cells(1, 4)).Value = _
           source.Range(source.Cells(1, 1), source.Cells(1, 4)).Value

    Dim row As Long
    row = 2

    Dim key As Variant, item As Variant
    For Each key In data.Keys
        For Each item In data(key)
            target.Cells(row, 1) = Split(key, "|")(0)
            target.Cells(row, 2) = Split(key, "|")(1)
            target.Cells(row, 3) = Split(item, "|")(0)
            target.Cells(row, 4) = Split(item, "|")(1)
            row = row + 1
        Next item
    Next key

End Sub

Private Function GetSubscriptions(source As Worksheet) As Dictionary

    Dim subscrips As Dictionary
    Set subscrips = New Dictionary

    Dim row As Long
    Dim cust As String, subs As String, starting As String, ending As String

    'Gather all the data as pairs of customer|subscription, starting|ending
    For row = 2 To source.UsedRange.Rows.Count
        Dim dates() As String
        cust = source.Cells(row, 1).Value
        subs = source.Cells(row, 2).Value
        'Valid customer/subscription?
        If cust <> vbNullString And subs <> vbNullString Then
            starting = source.Cells(row, 3).Value
            ending = source.Cells(row, 4).Value
            'Has an ending and starting date?
            If starting <> vbNullString And ending <> vbNullString Then
                Dim key As String
                key = cust & "|" & subs
                'New combo?
                If Not subscrips.Exists(key) Then
                    subscrips.Add key, New Collection
                    subscrips(key).Add starting & "|" & ending
                Else
                    subscrips(key).Add starting & "|" & ending
                    Set subscrips(key) = MergeDates(subscrips(key))
                End If
            End If
        End If
    Next row

    Set GetSubscriptions = subscrips

End Function

Private Function MergeDates(dates As Collection) As Collection

    Dim candidate As Long, index As Long
    Dim values() As String, test() As String
    Dim merge As Boolean

    For index = 1 To dates.Count
        values = Split(dates(index), "|")
        'Check to see if it can be merged with any other row.
        For candidate = index + 1 To dates.Count
            test = Split(dates(candidate), "|")
            If CDate(test(0)) >= CDate(values(0)) And _
               CDate(test(0)) <= CDate(values(1)) Or _
               CDate(test(1)) >= CDate(values(0)) And _
               CDate(test(1)) <= CDate(values(1)) Then
                dates.Remove candidate
                merge = True
                Exit For
            End If
        Next candidate
        If merge Then Exit For
    Next index

    If merge Then
        'Pull both rows out of the collection.
        dates.Remove index
        values(0) = IIf(CDate(test(0)) < CDate(values(0)), _
                        CDate(test(0)), CDate(values(0)))
        values(1) = IIf(CDate(test(1)) > CDate(values(1)), _
                        CDate(test(1)), CDate(values(1)))
        'Put the merged date range back in.
        dates.Add values(0) & "|" & values(1)
        'Recurse.
        Set MergeDates = MergeDates(dates)
    End If

    Set MergeDates = dates

End Function

它确实需要通过数据验证、错误捕获等来充实它，而且它目前只是将结果数据放在一个新的工作表上。所有工作都在 GetSubscriptions 中完成函数，因此您可以从中获取返回的 Dictionary 并对其中的数据执行任何您需要执行的操作。