Sort data from a JOIN(COLLECT( formula within a cell

anna_ashby
anna_ashby ✭✭
edited 06/15/22 in Formulas and Functions

I have a formula in one of my sheets that looks out across multiple columns and populates the cell with all values present in the columns for that row. I simplified the column names to make it easier to read, but here is the formula:

=JOIN(COLLECT([Data1@row:Data5@row, Data1@row:Data5@row, <> ""), ", ")

My question: Is there a way to have this data sort itself in ascending order when it appears in the cell?

I haven't been able to find a way to do this so far and would love input on what I can try to make this work.

Thanks so much!

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Unfortunately this is currently not possible, but it is a great idea. Feel free to Submit a Product Enhancement Request.

     

    In the mean time, you would need to find a way to sort the data BEFORE joining it.

  • Maaik Meijerink
    Maaik Meijerink ✭✭✭✭✭
    edited 06/15/22

    Any news about sorting within a cell with a formula ?

    I already submitted this product Enhancement Request several times ...

    For some goals I need to have dates from another sheet in a cell. No problem to get these, but sorting is no possible, so now I have them in order of appearance of the COLLECT formula. These data is entered randomly in different columns so first sorting them in the source-sheet is not the solution.


    Example (dutch date-format):


    [Planned dates]2:

    01-03-2022

    01-01-2022

    13-12-2022

    10-05-2022

    01-05-2022


    of course the desired result should be:


    [Planned dates]2:

    01-01-2022

    01-03-2022

    01-05-2022

    10-05-2022

    13-12-2022


    to instantly see the order of dates, you have to look closely. With hundred of rows which get updated by this formula regurarly, I have to implement webhooks via an API. Which of course can be solved, but this work-around is way to much/heavy for such a small feature.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!