Join distinct collect
=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
Best Answer
-
Thank you
Answers
-
Are those extra numbers in any cell that you are referencing?
-
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.
-
Are you able to provide a screenshot of the source data?
-
And what about the sheet where the formula is?
-
Try creating a filter on the source sheet to see if any of those columns may contain one of those numbers.
-
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.
-
Which columns exactly are you pulling in?
-
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
-
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.
-
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))
-
Thank you
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!