Function like UNIQUE in Excel?

Sherry Fox

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.

L_123

distinct()

Sherry Fox

@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.

L_123

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

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???

L_123

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

@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!

L_123

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

@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<>"")))