google-sheetsformulasequence

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


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


Solution

  • Here's one approach you may test out:

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

    enter image description here