Join Collect Distinct

Anthony Barthelemy

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

Genevieve P.

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

Bassam Khalil

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)

Genevieve P.

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

Thanks All! That worked

Parent Formula:

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

Child Formula:

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

Anthony Barthelemy

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.

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

@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.

Bassam Khalil

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.

Eric Gonzales

https://community.smartsheet.com/discussion/comment/299167#Comment_299167

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.

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