# Join distinct collect

Options
✭✭✭✭

=JOIN(DISTINCT(COLLECT({pots 1 quantity}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 2 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 3 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 4 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10))

I would like to collect the distinct values from 4 columns but somehow it added 3 numbers that should not be there ex: it should be 1 to 7 but it adds 13,71, 73

What is incorrect about the formula

• ✭✭✭✭
Answer ✓
Options

Thank you

## Answers

• ✭✭✭✭✭✭
Options

Are those extra numbers in any cell that you are referencing?

• ✭✭✭✭
Options

13,71, 73 does not exist anywhere. It seems to have combined the numbers together. EX: 1 and 3, 7 and 1, 7 and 3

So I am not sure the best way to do this formula. I need it to look at the four columns and pull out the unique values from each of the columns with the same conditions.

The columns are not side by side either.

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot of the source data?

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

And what about the sheet where the formula is?

• ✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

Try creating a filter on the source sheet to see if any of those columns may contain one of those numbers.

• ✭✭✭✭
Options

They do not, I have checked.

=JOIN(DISTINCT(COLLECT({pots 1 quantity}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 2 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 3 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({POTS 4 QUANTITY}, {NUMBER}, STUDY@row)), CHAR(10))

It seems that the first, join collect distinct which is in the first column, is fine, but the next ones join the values that are collected.

• ✭✭✭✭✭✭
Options

Which columns exactly are you pulling in?

• ✭✭✭✭
Options

Pots 1 , Pots 2, pots 3, pots 4. I know in the formulas it has quantity added. It is because of privacy I needed to change column names

• ✭✭✭✭✭✭
Options

Your formula should be working. I don't see anything wrong with it. If you have applied a filter and it came up with zero rows for those three numbers but your formula is still pulling them, I would venture to say that there is a bug somewhere and it may be time to contact Support.

• ✭✭✭✭
Options

After some research I resolved the problem. we need to add an extra CHAR(10)

=JOIN(DISTINCT(COLLECT({pot 1}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10) + JOIN(DISTINCT(COLLECT({pot 2}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10) + JOIN(DISTINCT(COLLECT({pot 3}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10) + JOIN(DISTINCT(COLLECT({pot 4}, {number}, [STUDY]@row)), CHAR(10)) + CHAR(10))

• ✭✭✭✭
Answer ✓
Options

Thank you

• ✭✭✭✭✭✭
Options

And that makes perfect sense too. My apologies for not noticing that sooner, but I am glad you were able to get it sorted.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!