Help with DISTINCT Function
I need to create a list of unique names pulled from a column on a different sheet. I used this example to get things generally working. However, I'm not ending up with a truly unique set of names.
The formula in question is: =JOIN(DISTINCT(COLLECT({Fruit}, {Fruit}, @cell <> "")), ",") + ","
Here's an example of how my source data is setup. It is all in one column
COLUMN NAME: FRUIT
Row 1: Apple, Banana, Grape, Pear
Row 2: Grape, Pear
Row 3: Apple, Strawberry, Cherry, Grape
Resulting list:
Apple
Banana
Grape
Pear
Grape
Pear
Apple
Strawberry
Cherry
Grape
So I must be misunderstanding how the DISTINCT function works here. I suspect this is related to the fact that each cell has multiple values in it and maybe the original formula is intended for a column where each cell only has one value in it. Can anyone enlighten me on why this is not working as I expect, and if there is a way to dynamically create a unique list from a column where cells may have multiple values in them?
Answers
-
Your formula is written correctly, so it should be working fine.
Which rise the question about how your fruit column is set up?
Is it a string text column or is it a multi select drop down list? (Is it created out of a JOIN(COLLECT()) formula)?
If it's a string text column, then Collect will grab all those values as 1 item, and not a list of items. So DISTINCT will see all theses strings as distincts strings of text.
Hope it helped!
-
Thanks for the reply @David Joyeuse . This was just an example of what I'm trying to do. The Fruit column, as well as the actual I'm working on are text/number columns.
Any thoughts on how to approach this so that I end up with a list of unique values? I'm not sure how to take the string of values as they currently exist in each cell and get them into an array so that DISTINCT just sees one big list of names to work with.
-
I was able to answer my own question. My use of this example was correct and gave me a column with one name in each cell, although many names were duplicates. Then I just had to find a way to create a list of unique names from the column that had duplicates. This post gave me that answer, and now I have one column with a list of completely unique names.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!