Formula for nonduplicate values
Hi,
Is there any formula to populate only 1 value of each value listed [Column1]?
I.e., column 1 has something like the below,
45
56
45
55
55
56
46
56
Is there any formula to only show the below?
45
46
55
56
Thank you!
Best Answers

Hi @A Rose
Yes! But it will gather together all the separate values into one cell, is that what you were looking for? There's a DISTINCT Function we can use, like so:
=JOIN(DISTINCT([Column1]:[Column1]), ", ")
However keep in mind that all values must be of the same data type in order for the function to calculate (ex. all must be numerical, or all must be text).
Let me know if I've misunderstood what you're looking to do and I'd be happy to help further.
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

Hey @A Rose
Building on Genevieve's answer, to have the answers wrapped try CHAR(10) as the delimiter.
=JOIN(DISTINCT([Column1]:[Column1]), CHAR(10))
The CHAR(10) is ASCII code for a new line.
You must set your column format to wrapped text for the data to appear 'wrapped'.
cheers
Kelly
Answers

Hi @A Rose
Yes! But it will gather together all the separate values into one cell, is that what you were looking for? There's a DISTINCT Function we can use, like so:
=JOIN(DISTINCT([Column1]:[Column1]), ", ")
However keep in mind that all values must be of the same data type in order for the function to calculate (ex. all must be numerical, or all must be text).
Let me know if I've misunderstood what you're looking to do and I'd be happy to help further.
Cheers,
Genevieve
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

That worked!
would be best though if we can split it in seperate row, either 1 value per row, or when doing "Wrap" it should be on seperate row so we can read it better.
Btw. it worked even though it's all a value, and a number. I.e. "Value 55"
Thank you!

Hey @A Rose
Building on Genevieve's answer, to have the answers wrapped try CHAR(10) as the delimiter.
=JOIN(DISTINCT([Column1]:[Column1]), CHAR(10))
The CHAR(10) is ASCII code for a new line.
You must set your column format to wrapped text for the data to appear 'wrapped'.
cheers
Kelly

Hi @Kelly Moore ,
Amazing! thanks so much!
Can we go further and have it sorted A to Z in the formula?
Thank you!

One value / row is easier than sorting it directly. You can do it with 2 formulas.
1st cell top of return column: =index(distinct([Col1]:[Col1]),1
2nd cell and dragged down: =index(distinct([Col1]:[Col1]),1+count(CurrentColumn$1:CurrentColumn1
then use a report to sort the results
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!