Function like UNIQUE in Excel?

Options
Sherry Fox
Sherry Fox ✭✭✭✭✭✭
edited 07/27/23 in Formulas and Functions

Does Smartsheet have a function similar to the UNIQUE function available in Excel? The function allows you to select a range, and then provides a unique list from that list. In Excel Combining: SORT, UNIQUE & FILTER will provide a unique list excluding blanks that is alphabetized. I am constantly using that function within Excel, and do not know if there is something similar within Smartsheet for me to use.

Sherry Fox

Business System Data Analyst

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Options
  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @L_123 ,

    I tried using DISTINCT in Smartsheet the way I would use UNIQUE in Excel, and it does not seem to work. These are text values I am looking at and I am getting "Invalid column type". My column is set to text/number.

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    distinct returns a range.

    example:

    Index(distinct({range},2) will return the second occuring unique value out of the range. If you want to return all unique cells into a single cell you can use

    =join(distinct({range}),char(10)

    If you want to return them in separate cells you need 2 formulas. For the top cell it will be

    =index(distinct({range}),1)

    then all cells below will be a version of

    =iferror(index(distinct({range},1+count([current column name]$1:[current column name]1),"")

    dragged down to autocomplete.

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    That does not sound like a user-friendly function in comparison with UNIQUE in Excel being an array function. Okay I tried it per your instructions, and the formula for my first row is:

    =INDEX(DISTINCT({PM_List}), 1)

    Which resulted in a text value. This one happened to pull in a name that begins with Q first. And that name is first on the source data file. Is there any way to alphabetize this? The formula in the next cell and pulled down is:

    =IFERROR(INDEX(DISTINCT({PM_List}, 1 + COUNT(PM$1:PM1), "")))

    And FYI, I COPIED your formula, and then replaced your ranges with mine, but for the sheet the formula is on, when I clicked "enter", the square brackets disappeared! Below is the result of my formula. The column I reference on the other sheet is called "PM_List". What did I do wrong???


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 07/28/23
    Options

    I had a couple issues with parenthesis, apologies.


    =IFERROR(INDEX(DISTINCT({PM_List}), 1 + COUNT(PM$1:PM1)), "")


    Let me know if this works.

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @L_123 ,

    Yes, that revision worked perfectly. However the list is not alphabetical, is there anyway to make it alphabetical? I tried SORT like in Excel, but that does not work. But I am thrilled it is working!!! Thank you!

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    There isn't a very easy way to sort via formula. Typically what I would do is let there be the slop in the worksheet, then post into a report that is sorted. This has a couple issues, but you can still have users edit/maintain and input values for the most part.

    I recommend submitting an idea for this on the forum below:

    https://community.smartsheet.com/categories/smartsheet-product-feedback-&-ideas?sort=top

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @L_123 ,

    I get it, I was just hoping for a clean option to be able to "auto-sort" within the formula results by using another function too. This would be the formula I would use (for an array) that would ignore blank values, and put my results in alphabetical order. I am happy to get a unique list via a formula, even if it has to come in 2 separate formulas (that is weird). Thanks for your help!

    =SORT(UNIQUE(FILTER(B2:B12, B2:B12<>"")))

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!