No help needed - Just a question to ask about : Concatenate() a list of sequential and/or non-sequential numbers?

4

Hey @Pch, nice to see you back!

You got me curious and of course you overcomplicated it :smiley: Here’s a much simpler solution:

Once you have your list of sorted numbers (Numbers (lists; sorted) in my doc), here are the steps:

  1. Iterate over the list and remember positions of items that start a new range. For that, don’t iterate over the numbers themselves

    Numbers.FormulaMap(CurrentValue...)

    but iterate over indexes, i.e.

    Sequence(1, Numbers.Count()).FormulaMap(
  Numbers.Nth(CurrentValue)...
)

    To see where a new sublist starts, check if

    .Nth(CurrentValue - 1) != .Nth(CurrentValue) - 1

    Also append the Count + 1 value to the resulting list because we’ll need it for the next step.

    The full formula:

    Sequence(1, thisRow.Numbers.Count()).FormulaMap(
  If(
    thisRow.Numbers.Nth(CurrentValue - 1) = thisRow.Numbers.Nth(CurrentValue) - 1,
    List(),
    CurrentValue
  )
).ListCombine(thisRow.Numbers.Count() + 1)

    See how we return List() instead of blank "" within the If() — ListCombine() would eat empty lists but not empty values.

  2. Now that we have our “break points”, let’s collect a list of 2-item lists for each range’s start and end. If it’s a single-item list, both the start and the end are just going to be the same value:

    Sequence(2, thisRow.Indexes.Count()).FormulaMap(
  List(
    thisRow.Numbers.Nth(thisRow.Indexes.Nth(CurrentValue - 1)),
    thisRow.Numbers.Nth(thisRow.Indexes.Nth(CurrentValue) - 1)
  )
)

  3. Lastly, let’s just iterate over each range, format it with or without an arrow, and join together:

    thisRow.[From-to pairs].FormulaMap(
  If(
    CurrentValue.First() = CurrentValue.Last(),
    CurrentValue.First(),
    Format(
      "{1} → {2}",
      CurrentValue.First(),
      CurrentValue.Last()
    )
  )
).Join(", ")

That’s how you break this calculation into logical steps. No need to calculate missing numbers, no need to compare each one with a different one or whatnot. Just left to right, N-th one with the (N-1)-th one. Collect ranges, then format ranges.

5 Likes