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

Hi All :wave: !!!

I know I’ve been away for quite some time (Life just happened :sweat: ) but I always knew that, at some point, I would have the opportunity to get back on having fun building docs, and here, by the same occasion :wink: .

Well, this kind of happened a month ago (or so) when I began to create my very first personal doc since I went AWOL :blush: .

At some point, I evidently hit a wall but found a solution which works for that specific doc :blush:

So, completely out of curiosity (but also because I still need and want to learn :wink: ) I would like to know if I could have done this differently (and maybe in simpler way as I still tend to overcomplicate things where it’s not necessarily needed :smirk:) :blush:

Any idea(s), would be more than appreciated :grin: !

I hope I shared this doc correctly (as it has been a while too) and didn’t make mistakes in the sharing options :sweat_smile:

I also really want to congratulate everyone for the magistral evolution of Coda :grin: :tada: :+1: !!!
Like the very first time I used Coda : The more I use it, the more ideas I get and therefore, the more I want to use it :grin: !
Plus, even though “my Coda” is a little bit rusty, I was more than happy to discover that I didn’t forgot that much during the months I was away and was still able to create a doc from scratch without too much trouble :+1: ! This, for me, says a lot about how great Coda is :grin: !

Thank you very much for reading :blush: !

Welcome back @Pch, your presence was missed! Glad that you were able to circle back.

Please adjust the sharing permission on this doc. Right now other people cannot view your example

Brief history of how to share/embed in the Coda community (click to expand)

(2020-02-07) https://community.coda.io/t/exchange-rates-and-setting-up-tables-to-use-them/13705/9 - They changed it for security reasons. The Share/Embed modal needs better signage to reflect this change, imho
(2020-02-13) https://community.coda.io/t/mega-trick-formatted-tooltips-for-your-table-items-with-json/13813/3 - something changed on that front. Previously embedding automatically added “can view by link”
(2020-05-04) https://community.coda.io/t/combining-2-different-columns-from-2-different-tables-in-a-lookup/15649/5 - how to
(2020-06-27) https://community.coda.io/t/value-for-new-row-not-working-properly/16918/4 - There are two ways
(2020-09-02) https://community.coda.io/t/how-to-use-24-hour-in-formula/18117/4 - anyone with link can view picture
(2020-09-03) https://community.coda.io/t/counting-occurence-tags-from-a-main-table-to-another-table/18115/4 - anyone with link can view picture
(2020-10-01) https://community.coda.io/t/show-coda-sample-in-this-community/18671/2 - how to embed

1 Like

Ahahah :sweat_smile: ! It should be working now :sweat_smile: !

Thank you very much for your help with sharing/embedding my doc :grin: ! And your very kind words too :blush: !

1 Like

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.

4 Likes

Wow @Paul_Danyliuk,

This is again a masterpiece :chart_with_upwards_trend: of “formula fitness” :running_man: :running_woman:

Thanks, not only for the solution, but even more to make it understandable for people like me :thinking:

1 Like

Ahahah :yum: ! I knew it :wink: !

FormulaMap() is a formula I actually have a hard time to “re-learn” how to use (correctly) and when. But I’m getting there :wink: !
I was pretty certain while building my example doc that I should’ve looked in that direction but I just couldn’t find a way around it…

This is, like always :wink: , a way better and prodigiously simpler solution to my initial problem :grin: !

Thank you very very very much for taking the time to look at this :+1: :grin: !
I’m gonna try and implement it to my doc right away :grin: !

2 Likes