Formula Translation

Cristian

Is there an equivalent formula for "UNIQUE" that I can use in Smartsheet?

Here's the excel formula I am using: =unique(C2:C130)

The output is six unique elements taken from column C.

Mike Wilday

This is an interesting question and one that leans heavily on how Smartsheet is fundamentally setup.

Unlike Google Sheets/Excel smartsheet is row-dependent spreadsheet SASS solution. Each row of a smartsheet contains functions and powers that make it very useful in most circumstances but not so much in ones like these where we're collecting data from a larger list.

This discussion is addressed by @Paul Newcome here:

https://community.smartsheet.com/discussion/54816/extracting-a-list-of-unique-values-from-a-list-on-another-sheet

But its not a native feature of smartsheet.

Paul Newcome

@Mike Wilday Thanks for the tag.

@Cristian You would use this:

=IFERROR(INDEX(DISTINCT({Range}), 1), "")

You can use a helper column with the numbers 1 - whatever manually entered and reference that in place of the 1, but either way you would need to "pre-fill" a number of rows to accommodate the maximum number of entries you anticipate (plus a buffer).

Paul Newcome

Another option depending on how exactly you plan to use the unique list would be to create a report and then group by the column you want the unique entries from. If all you are doing is basic counts or sums/averages of other columns based on the unique entries for something like a chart in a dashboard, that can be done in a report and definitely scales better than having to manually anticipate how many unique entries you might have.

Mike Wilday

Yes, @Paul Newcome's thoughts about grouping in a report are spot on. Reports can do that work for you. Here are a couple of links to a summary of what reports are, and specific help to configure grouping.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

https://help.smartsheet.com/articles/2482082-configure-grouping-to-organize-results-in-report-builder