# Formula for non-duplicate values

Options
✭✭✭✭✭

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!

Tags:

Options

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

• ✭✭✭✭✭✭
Options

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

Options

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

• ✭✭✭✭✭
edited 12/27/21
Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

Hi @Kelly Moore ,

Amazing! thanks so much!

Can we go further and have it sorted A to Z in the formula?

Thank you!

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!