Unique() or Distinct() function for finding unique values

zealvert

Hi,

It would be very helpful if Smartsheet redesigns the DISTINCT function so it can be used not just as an argument, but also at the top level to pull all unique values from a column.

For example, if a column titled Customers has many duplicate names, we should be able to use a formula like:
=DISTINCT([Customers]:[Customers])

When converted to a column formula, it would dynamically return a list of unique customers.
Without this, we have to create multiple complex formulas to achieve the same result, which is very cumbersome.

A major benefit of this capability would be the ability to use (for example) the RANKEQ() function to quickly find the order in which our customers spent the most on our products or services.

Genevieve P.

Hi @zealvert

You can use the DISTINCT function in this way, but you're right that it does need JOIN in front to indicate how you want the value to appear.

Try this:

=JOIN(DISTINCT(Customers:Customers), ", ")

This will bring all distinct values into one cell.

If you're looking to parse out the list into many separate rows/cells, I would recommend creating a Row Report then grouping by that Customers column. You can then use the Summary feature for your Count of how many rows exist for each customer.

Cheers,
Genevieve

Paul Newcome

@zealvert To expand on @Genevieve P.'s comment, you can parse this out into a list in a sheet (for additional metrics if the row report doesn't give you what you need) using an INDEX function.

=INDEX(DISTINCT([Column Name]:[Column Name]), 1)

The 1 at the end would change to a 2 to output the second distinct entry from the column, 3 for the third, so on and so forth. I usually insert a text/number column (called "Number") and manually enter the numbers one through however many I think I will need plus a buffer, then use a cell reference in place of a hard-coded number.

=INDEX(DISTINCT([Column Name]:[Column Name]), Number@row)

And finally… To keep things clean, I will use an IFERROR to leave rows blank that are numbered to high (but keeps my buffer).

=IFERROR(INDEX(DISTINCT([Column Name]:[Column Name]), 1), "")