Model / Year formula fails when a sequence starts stops then starts again

Question

I have a long series of motorcycle makes and models and my formula breaks when a model is sequential, non sequential then back to sequential years. Please help fix this formula or provide a different formula for google sheets. Thank you

Goal: Identify when there’s a break in the sequence and start a new sequence when it happens. Properly combine sequential years into ranges (e.g., 1974-1977). Add commas between non-sequential years, while keeping separate ranges when necessary.

Here's an example spreadsheet. I highlighted the cell red that has the incorrect formula result and added a comment with what the correct result should be. https://docs.google.com/spreadsheets/d/1lVEXBbiYVUmv0TUZl1K2Qo1fmizRaNMZk8VCaW4ON0E/edit?usp=sharing

Answer 1

I would just advise that whichever solution you use, try not to use any volatile functions if possible, which includes OFFSET. For the solution provided by @rockinfreakshow, I think this would work:

=scan(,sequence(rows(B2:B)),lambda(a,i,
   let(c,index(B:B,i+1),
     if(c="",,
     if(index(A:A,row(c))<>index(A:A,row(c)-1),c,
     if(c=index(B:B,i)+1,
       regexreplace(a&"","(\d{4})(-\d{4})?$","$1-"&c&""),a&", "&c))))))

And similarly with the solution provided by @z..:

=SCAN(,
   SEQUENCE(ROWS(B2:B)),
   LAMBDA(prev, i,
     LET(cur, INDEX(B:B, i+1), 
       IF(cur = "", , "" & 
         IF(  
           INDEX(A:A, i+1) <> INDEX(A:A, i),
           cur, 
           IF(
             INDEX(B:B, i) + 1 <> cur,
             prev & ", " & cur,
             REGEXEXTRACT(
               prev,
               IF(REGEXMATCH(prev, ","), ".*, \d+", "\d+")
             ) & "-" & cur
           )
         )
       )
     )
   )
 )