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:

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 12/24/21 Answer ✓
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 12/24/21 Answer ✓
    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

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

    Hi @Genevieve P.

    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!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    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

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Hi @Kelly Moore ,

    Amazing! thanks so much!

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

    Thank you!

  • L_123
    L_123 ✭✭✭✭✭✭
    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!