新新200新新新新新200新新200新新200新新200新新200新新200新新200新新新新新 [英] Merge rows with connecting 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可用，但是没有线索在该程序中开始使用这样的问题。
动态解决方案可能可能，但是如果数据集很大，它可能会将事情下降一段时间，因为您需要在每次更改单元格时运行它。
基本上，我可以看到的最好的方法是创建您的customer_id和subscription_id的唯一键，然后收集该键下的所有日期范围并合并。
这样的东西应该让你开始（需要参考Microsoft Scripting Runtime）：
Public Sub LinkSubscriptionDates（ ）
Dim数据作为字典，源为工作表，目标为工作表
设置源= ActiveSheet
设置数据= GetSubscriptions（源）
设置目标= source.Parent.Worksheets.Add
'复制标题
target.Range（target.Cells（1，1），targe t.Cells（1，4））。Value = _
source.Range（source.Cells（1，1），source.Cells（1，4））值
Dim行As Long
row = 2
Dim key As Variant，item As Variant
对于每个键在data.Keys
对于每个项目在数据（键）
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
下一个项目
下一个键
End Sub
私有函数GetSubscriptions（源作为工作表）作为字典
Dim subscrips As Dictionary
设置subscrips =新字典
Dim row As Long
Dim cust As String，subs As String，starts As String，以As String结尾
'将所有数据作为成对的客户|订阅，开始|结束
对于row = 2到source.U sedRange.Rows.Count
Dim dates（）As String
cust = source.Cells（row，1）.Value
subs = source.Cells（row，2）.Value
有效的客户/订阅？
如果cust<> vbNullString和subs<> vbNullString然后
starting = source.Cells（row，3）.Value
ends = source.Cells（row，4）.Value
'有一个结束和开始日期？
如果启动<> vbNullString结束<> vbNullString然后
Dim键As String
key = cust& | &安培; subs
'新组合？
如果没有subscrips.Exists（key）然后
subscrips.Add key，New Collection
subscrips（key）.Add starting& | &安培;结束
Else
subscrips（key）.Add starting& | &安培;结束
设置子行程（key）= MergeDates（subscrips（key））
End If
End If
End If
下一行
设置GetSubscriptions =子项目
结束函数
私有函数合并日期（日期作为集合）作为集合
重要的候选人As Long，index As Long
Dim值（）As String，test（）As String
Dim merge As Boolean
对于index = 1 To dates.Count
values = Split（dates（index） ，|）
'检查是否可以与任何其他行合并。
对于候选者=索引+ 1到dates.Count
test = Split（dates（候选），|）
如果CDate（test（0））> = CDate 0））和_
CDate（test（0））< = CDate（values（1））或_
CDate（test（1））> = CDate _
CDate（test（1））< = CDate（values（1））然后
dates.Remove候选人
merge = True
退出
结束If
下一个候选人
如果合并然后退出对于
下一个索引
如果合并然后
'从集合中拉出两个行。
dates.Remove index
values（0）= IIf（CDate（test（0））< CDate（values（0）），_
CDate（test（0）），CDate （值（0）））
值（1）= IIf（CDate（test（1））> CDate（values（1）），_
CDate（test（1）），CDate值（1）））
'将合并的日期范围重新放入。
dates.Add values（0）& | &安培;值（1）
'重新出现。
设置MergeDates = MergeDates（日期）
结束If
设置MergeDates =日期
结束函数
它真的需要通过数据验证，错误捕获等来解决，目前只将结果数据放在一个新的工作表上。所有的工作都在 GetSubscriptions 函数中完成，所以你可以从中获取返回的字典，然后做任何你需要处理的数据。
I've got a large Excel sheet with customer and subscription data. From this table I would like to merge records/rows with connection stop_ and start_dates and show the result in a new worksheet. A simplified version of the data is shown below.
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
So, the formula has to check the customer_id and the subscription_id. When there is a match between two or more rows in the sheet and the stop_date of one of the rows overlaps with the start_date of the other row, then after the extraction and merging, one new row must be shown with the start_date of the first and the stop_date of the other row. This also has to work if there are multiple rows with connecting dates. All the rows that don't match these criteria stay the same after the extraction. So the result will be like this:
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
A dynamic solution would be ideal while new data will be added to the original sheet. While I know this is possible when you're certain that the rows you're looking for are always below each other, this is not the case here and it wouldn't give you a very dynamic solution.
So some kind of array function would be needed in Excel I guess but after searching a lot I couldn't find a suitable solution. I've also got MATLAB available but no clue where to start in that program with a problem like this.
A dynamic solution may be possible, but if the dataset it large it might bog things down quite a bit because you'd need it to run every time a cell was changed.
Basically the best way I can see to approach this is to create unique keys out your customer_id and subscription_id, then collect all of the date ranges under that key and merge them.
Something like this should get you started (requires a reference to 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
It really needs to be fleshed out with data validation, error trapping, etc., and it currently just puts the resulting data on a new worksheet. All the work gets done in the GetSubscriptions function, so you can just grab returned Dictionary from that and do whatever you need to do with that data in it.
这篇关于新新200新新新新新200新新200新新200新新200新新200新新200新新200新新新新新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
