Find consecutive strings of numbers from out of order list, and calculate longest string

Hi I am trying to find a solution to problem I want to solve on my parkrun results spreadsheet. The definition of the calculation I need to make is: 'The total streak of finish positions, but starting anywhere, in any order. e.g. If you have finished: 123, 120, 304, 122, 201, 121 It would be 4, since you have: 120,121,122 and 123.

I have just manually checked my 'floating finish position' it is 24 - If anyone might be able to find a way to achieve this in an Excel formula, I would be really grateful. I have tried and failed!

Thanks!

Hi. I have uploaded a file, but when I try to attach it is just blank.

Apologies, I was about to post those, as the other website pointed it out. Thanks.

Screenshot 2020-04-03 at 15.14.45.png

I get this when trying to attach.

It now seems to have attached anyway 🤷🏻*♂️ It's the file called 'Example'.

Someone else suggested as per below, which will likely work and is great, but would really like to try and achieve it without extra columns or vbA, if at all possible

If your finishing positions are all in one column you will need a couple of helper columns to facilitate the calculation. Firstly you want a column of unique finishing positions sorted in ascending order. Assuming your finish positions are in column A, in Google Sheets, I'd use =SORT(UNIQUE(A1:A999)) and I'm sure Excel will have some similar functions.
Now you have your finish positions in column A and a sorted list of unique finish positions in, say, column B.
In column C we'll want to make a column that checks if the number in the column to the left is consecutive or not, so put a "0" in C1 because we know that's the start of a consecutive run, then in C2 the formula =IF(B2-B1=1,C1+1,0) will check if the next number down is consecutive; if it is it adds 1 to the run so it counts up and if it's not it'll put a zero to indicate the start of a new run. Then just drag that formula down for however many rows you need.
Finally, find the highest number in column C and add 1 using =MAX(C1:C999)+1

Wow, that worked perfectly. Thanks so much! One more thing, might you also be able to get the separate 'All Completed Runs - FFP-I' worksheet working as well? Thanks again, so much!

Hi. This is almost there. The only thing I would like to change is to have all of my finishing positions listed in column A and then in Column B all of the corresponding first venues at which this was achieved and in C all of the corresponding dates on which they were achieved - but only for the ones that are part of a streak.

Therefore, there should be a list in Column A of all of my unique finishing positions, going from 7 (my highest finishing position) down to 404 (my lowest finishing position) and then in B and C the corresponding first venues and dates on which these were achieved - only for finishing positions that are part of a streak. This will then result in a comprehensive list of all the position streaks I have done. Thanks so much.

I have attached a new example with this in a manual format, in the 'Manual' worksheet.

That is absolutely brilliant. Thank you so much, it is now perfect!!

Hi again. When putting this into my full document, I have a spill error. I have put in those two formulas, but getting a spill error. Perhaps something to do with the blank cells?

I have now resolved this. Thanks again.

I hadn’t filled down the formula in column B. Thanks

How do I mark as resolved, please?

Hi. I can’t see this option on mobile version?