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

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

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
Check out the Formula Handbook template!