Identify first occasion of consecutive dates

[ollyhughes1982]

Hi,

I have a spreadsheet of my runs and the dates on which I did them. I am trying to figure out a way to find out where I have done runs on consecutive numbers of dates - I am struggling to come up with a formula to do this. I know that it is likely some sort of FILTER(UNIQUE combined functions that are needed, but haven’t been unable to work it out.

- Firstly, I want to list the first occasion where I did runs on two consecutive days.

- Secondly, I want to list the first occasion where I did runs on three consecutive days.

- Thirdly, I want to list the first occasion where I did runs on four consecutive days.

These will be on three separate worksheets (see image and attachment). I only want each spreadsheet to show anything if at least one run has been completed, otherwise leave the coloured cells blank. For illustration I have highlighted the rows in corresponding colours on the data source (first) worksheet.

Here is a link to the file (it’s small and will open quickly): Headers.xlsx

Thanks in advance!

Olly.

 

[ollyhughes1982]

Also posted on Excel Forum: Identify sequences of consecutive dates

 

[ollyhughes1982]

I have now come up with a formula that correctly identifies the streaks and numbers them, in a helper column (column GE).

The updated file is located here: Headers_v2.xlsx

Now, all I need is a formula that will pick up the earliest occurring streak of each type (2, 3 and 4) and enter that earliest streak's event names and dates into each of the relevant 3 worksheets cells.

I have manually populated the three sheets with the entries I would expect to see. I'm pretty sure it is some sort of filter function I need, but I can't figure it out.

Thanks!

 

[ollyhughes1982]

This has now been solved on Excel Forum (post #20). Thanks.

 

[Fluff]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[ollyhughes1982]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Ok, apologies I wasn’t sure how to explain as it was provided in an uploaded file, so thought I would pin point where it was. Thanks