Join Collect Distinct

Options

Smartsheet Community:

Not Sure what I am doing Wrong but I am trying to Collect Data in another Sheet only Once. Therefore if it appears twice in that Sheet in that Colum, it should only Collect it Once.

Current Formula: =JOIN(DISTINCT(COLLECT({Jira Key}, <>""), ", "))

Results: Incorrect Argument

Thanks

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Ray B

    The COLLECT function first has the Range listed that you want to pull data from. Then you list the Range with Criteria, and what the criteria is.

    This means that since the range you want to pull is the same as the range with your criteria, you actually need to list it twice:

    COLLECT({Jira Key}, {Jira Key}, <>"")


    You also have the comma that should appear between each Jira Key in the wrong spot, it should be before the very last parentheses.


    Try this:

    =JOIN(DISTINCT(COLLECT({Jira Key}, {Jira Key}, <>"")), ", ")


    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/27/21
    Options

    Hi @Ray B

    Hope you are fine, please try to use Index With Collect Function. if you like i can do it for you but i need you to share me as an admin on a copy of your sheet (after removing or replacing any sensitive information)

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Ray B

    The COLLECT function first has the Range listed that you want to pull data from. Then you list the Range with Criteria, and what the criteria is.

    This means that since the range you want to pull is the same as the range with your criteria, you actually need to list it twice:

    COLLECT({Jira Key}, {Jira Key}, <>"")


    You also have the comma that should appear between each Jira Key in the wrong spot, it should be before the very last parentheses.


    Try this:

    =JOIN(DISTINCT(COLLECT({Jira Key}, {Jira Key}, <>"")), ", ")


    Cheers,

    Genevieve

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭
    Options

    Thanks All! That worked


    Parent Formula:

    =JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>"")), ", ")

    Child Formula:

    =JOIN(DISTINCT(COLLECT({Jira Key}, {Jira Key}, <>"")), ",")

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭
    edited 07/28/21
    Options

    Is there a way to add Commas to the Count in this Formula:

    Formula:

    =SUM(CHILDREN()) + " Fiber Used Out Of " + (SUM([Fiber Used (Feet)]2, " ") + " Feet")

    Result: 3134 Fiber Used Out of 12000 Feet

    Result I want: 3,134 Fiber Used Out of 12,000 Feet

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Ray B

    No, since the formula is both creating the SUM and then adding this number to text values, it cannot format the number to have a thousands format with commas. The number is being turned into a text string and cannot have a format applied.

    @Bassam Khalil, can you think of a way to add in a comma? I'm wondering if we can use MID and SUBSTITUTE to add it in, but this might be getting too complicated.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Genevieve P. & @Ray B

    Yes it's possible to add comma in easy way by creating a helper columns for the numbers that outcomes from the 2 sum functions and after adding the comma we can create the text.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Genevieve P. & Ray B

    I created a sample solution to add the Thousand formatting comma to a number:

    the formulas are:

    1- Number of Integer Characters

    =FIND(".", [Original Number]@row) - 1
    

    2- Total Number of Characters

    =LEN([Original Number]@row)
    

    3- Number With Comma

    =IF([Number of Integer Characters]@row = 8, MID([Original Number]@row, 1, 2)) + "," 
    + MID([Original Number]@row, 3, 3) + "," + MID([Original Number]@row, 6, 3) 
    + RIGHT([Original Number]@row, ([Total Number of Characters]@row 
    - [Number of Integer Characters]@row))
    

    and we can develop this formula to deal with any size of number.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Eric Gonzales
    Options

    Is there a way to get this to work with multi select enabled? This formula works great in a contact column but only when one person is selected. Thanks!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Eric Gonzales

    Would you be able to provide more context around your sheet and final end-goal? It would be helpful to see screen captures, but please block out sensitive data.

    If you're looking to return every unique Contact from a contact column that has multiple people selected, the JOIN formula is going to read each cell as a unique cell and join the values together, versus reading the individual selections (does that make sense?)

    What I would do here is have a hidden column in your source sheet that first translates the contacts into Multi-Select dropdown values instead.

    =[Contact]@row

    Then in your other sheet you could simply use JOIN in another multi-select dropdown column, like so:

    =JOIN({Contact Helper}, CHAR(10))

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!