Filter distinct values only into new column

Options

Hello!

I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over "Distinct" to only give me the unique values of [(Cells Linked from TE Tracker)]

From what I've seen, there is no direct function that can do this, but has anyone figured out a work around to achieve this?

Thank you!

Best Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓
    Options

    Hi @sawuzie


    There's no direct way to do this. However, you can add a checkbox helper column to identify duplicates. You can use the formula =IF(COUNTIF([Cells Linked from TE Tracker]:[Cells Linked from TE Tracker], [Cells Linked from TE Tracker]@row) > 1, 1, 0). This will add a check in the duplicates column for all values which appear more than once. You can then add the Distinct column with the formula =IF([Duplicate]@row=0, [Cells Linked from TE Tracker]@row). The Distinct column will only have the distinct values listed.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Sorry about that. I forgot the DISTINCT function.


    =IFERROR(INDEX(DISTINCT(COLLECT([(Cells Linked from TE Tracker)]:[(Cells Linked from TE Tracker)], [(Cells Linked from TE Tracker)]:[(Cells Linked from TE Tracker)], @cell <> "")), [Helper Column]@row), "")

«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!