Hello Everyone,
I'm having a data file with around 15000 rows of data. I have, say 3 columns, Personal ID, Start date and Stop date. There are several events for a particular Personal ID.
Data looks like:
id startdate enddate
1 13-Mar-2012 9-Apr-2012
1 12-Apr-2012 7-May-2012
1 8-May-2012 4-Jun-2012
1 5-Jun-2012 29-Jun-2012
1 30-Jun-2012 30-Jul-2012
2 16-Sep-2012 29-Sep-2012
2 30-Sep-2012 13-Oct-2012
2 14-Oct-2012 10-Nov-2012
2 11-Nov-2012 8-Dec-2012
2 9-Dec-2012 5-Jan-2013
3 24-Jul-2012 20-Aug-2012
3 21-Aug-2012 17-Sep-2012
3 18-Sep-2012 15-Oct-2012
3 16-Oct-2012 12-Nov-2012
3 13-Nov-2012 21-Nov-2012
I need to find out whether there are any
1. Overlapping date ranges? or any
2. Gaps (missing dates) between an Initial start date and Last stop date? for a particular Personal ID.
I was searching for a solution since so long and found out at one forum the solution for this. The attached file gives desired out put and as expected (only limited to the data present in that file).
I copy pasted data (sorted by ID, then by start date, then by end date) from my worksheet into the file provided, I defined the "Data_Range" into Excel as per my data (~ 15000 rows) and ran the macro but the output has not all the Overlapping dates and Gaps. Output is improper when considering large data..
Please help me out at this stage..if I'm missing out anything or if I need to perform any task at some point like formatting, specifying any range or do I need to adapt the code?
or can anyone please provide a better approach for the same?
Please note: There are around 15000 rows. and the numbers keep on varying company to company.
I have seen that the code does not take the ID into account in deciding the missing dates ; also it does not detect overlapping dates between IDs.
So, You can ignore the second case where Overlapping dates between IDs is concerned; not required.
But the first case is mandatory; i.e. code must take ID into account in deciding the missing dates.
To make it simple, Both Overlapping dates and Missing dates should be Per ID, Not between IDs.
Thanks
-Arun
Bookmarks