Identify sequences of consecutive dates

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.
Master Sheet.png

- 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.

Sheet 4.png

These will be on three separate worksheets (see image and attachment). I only want each spreadsheet to show anything if at least one sequence 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.

I also want the runs to be unique if, possible. i.e. I don’t want the double or triple ones to show the first two and three dates from a quadruple sequence - if this is possible! I think I may need some of helper column to possibly add number sequences for the amount of consecutive dates?

Here is a link to the file (it’s small and will open quickly): https://1drv.ms/x/s!AtIvkRgReIvwjmUq...5sTJa?e=Ax2jS3

Also posted on Mr. Excel: https://www.mrexcel.com/board/thread...dates.1209295/

Thanks in advance!

Olly.

A helper column with this formula, say in GH4, copied down:

Formula:

Please Login or Register  to view this content.

Then this formula to return desired results:

Formula:

Please Login or Register  to view this content.

Good luck!

Thanks, that looks like it will work perfectly. Please could you help with the filter needed in the separate worksheets? I'd like to identify the first instance of each sequence type Thanks soo much for looking at this!

Thank you everyone. I will try all of these and confirm which is best for me. Thanks again!

Thanks. This doesn't seem to work for me. .Please see attached.

Thanks. I don't think I understand. All I want to do is show the event (venue) and corresponding date for each date sequence. Each separate worksheet should show he sequences as per the manually entered examples. Thanks, sorry for my ignorance.

last image, as could only upload 5:
Attachment 785868

It's similar to the two before, but with 4 entries, instead of two or three

These are the entries I would expect to see in the three separate worksheets (i.e. Each separate run of the first of each sequence length):
Attachment 785886
Attachment 785887
Attachment 785888

Thanks.

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

Screenshot 2022-07-03 at 13.54.00.png

The updated file is located here (and attached: 'Headers_v2'): https://1drv.ms/x/s!AtIvkRgReIvwjme2...JPMQZ?e=C0OxBu

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!

Originally Posted by Bernie Deitrick

I was confused - you were using 'run' meaning 'streak' as well as 'single event' - a run of runs. I have fixed the file now, using three columns of formulas.

Thanks for having a look at this. I am out at the moment, so will have a look later or tomorrow. Thank you

Hi, that’s brilliant and has now solved my problem. One last issue, is I now want to just provide a summary count of how many times I have done each. Would you be able to show how to do that, please? I have so far set it up as follows:

Headers WORKING.xlsx

I have tried the formula below:

=IF('All Completed Runs'!A4="","",IF(COUNTIF('All Completed Runs'!GE4:GE274,2)>=1,"Yes, "&COUNTIF('All Completed Runs'!GE4:GE274,2),"No"))

This works for the double headers, as they only go up to 2, but it doesn’t work for the the triple and quadruple-headers, as they also then include the double and triple-headers respectively in their counts. What I need to do is count the maximum number for each sequence and count on that.

Thanks again for all of your help.

Apologies, image didn't upload. Now attached.
Attachment 786070

I have now resolved this using a helper column as follows:
=CONCATENATE(GE4,", ",GE5,", ",GE6,", ",GE7)

This provides a pattern of the four-number sequences (e.g. 1, 2 for a run of 3, 1, 2, 3, 1 for a run of 3 and 1, 2, 3, 4 for a run of 4) and then do a countif for each, using these patterns as criteria.

Thanks.