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