Conditional consecutive date formula challenge

[Domski]

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.

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

 

[BobUmlas]

ctrl+shift+enter:
=MIN(IF(A2:A20=K1,B2:B20))
where K1 (formatted as text) contains the 00123456 or 00121212

 

[Domski]

ctrl+shift+enter:
=MIN(IF(A2:A20=K1,B2:B20))
where K1 (formatted as text) contains the 00123456 or 00121212

Thanks but that will only return the earliest date in the list for each employee. I want the earliest start date only taking into account records that have consecutive start and end dates going back from the most recent record.

Dom

 

[Domski]

Bump