Domski
Well-known Member
- Joined
- Jan 18, 2005
- Messages
- 7,292
Tricky one for you formula gurus.
I have data like below and for each individual I would like to work back from the most recent record and identify the earliest start date for consecutive records.
<tbody>
</tbody>
So for the two examples above the results would be:
<tbody>
</tbody>
I can do it by sorting the data and adding a helper column but was wondering if it was possible with a single formula.
Cheers,
Dom
I have data like below and for each individual I would like to work back from the most recent record and identify the earliest start date for consecutive records.
Pers Num | Start Date | End Date |
00123456 | 26/11/2007 | 31/03/2008 |
00123456 | 01/04/2008 | 30/09/2008 |
00123456 | 15/10/2012 | 31/03/2013 |
00123456 | 01/04/2013 | 31/07/2013 |
00123456 | 01/08/2013 | 31/12/2014 |
00123456 | 01/01/2015 | 31/08/2015 |
00121212 | 01/04/2007 | 02/09/2007 |
00121212 | 03/09/2007 | 25/11/2007 |
00121212 | 26/11/2007 | 31/03/2008 |
00121212 | 01/04/2009 | 31/01/2010 |
00121212 | 01/02/2010 | 31/12/2011 |
00121212 | 02/07/2013 | 08/12/2014 |
00121212 | 09/12/2014 | 31/12/2014 |
00121212 | 01/01/2015 | 31/12/2015 |
<tbody>
</tbody>
So for the two examples above the results would be:
Pers Num | Result |
00123456 | 15/10/2012 |
00121212 | 02/07/2013 |
<tbody>
</tbody>
I can do it by sorting the data and adding a helper column but was wondering if it was possible with a single formula.
Cheers,
Dom