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 connection 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-2013 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 afther 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. I hope someone could help me with this problem. Kind regards.
Aucun commentaire:
Enregistrer un commentaire