Return list of Unique Entries

Lisa Kennedy

Hi,

I have list people's names that repeats. Does anyone have a formula for returning those entries which are unique and or/an identifier of unique values (like a 1 that I could use in a helper column if the entry is unique.

Thanks,

Lisa

Paul Newcome

Ok. I am going to suggest doing this on a separate sheet.

In a text/number column I will call "Number" in this example manually enter numbers going down the rows starting with 1 and going down as far as you want to go.

The next column will be where your list goes, so you can make it whatever type of column is appropriate for the incoming data and use this:

=INDEX(DISTINCT({Column Containing Data}), Number@row)

Cleversheet

Generating a list of unique values in Smartsheet should not be hard to do, nor the guidance for it hard to find. Unfortunately, until now that's not been the case. Here is a solution that I believe will work flawlessly, though of course I welcome critique that will exposes its weaknesses so they can be resolved.

You need three primary elements to pull, in this example, the unique states from a sample list of contacts and display them sorted alphabetically in a report:

• a source sheet (this link is to a public-domain sample contacts listing, and none of the info is believed to refer to real people)
• what I call a uniquer sheet containing this column formula in the [UniqueStates] column, next to the [Uniquer] column that contains sequential numbers starting with zero: 
  • =IFERROR(IF(Uniquer@row > 0, (INDEX(DISTINCT({SampleContacts PickState}), Uniquer@row)), IF(Alert@row = "Alert", "+Need more rows in Uniquer", "")), "")
• the resulting report. Any applicable edit in the source data will ultimately appear in the report. Obviously, this report can be included in a dashboard or wherever else useful to you.

Note that cell Alert1 in the Uniquer sheet contains the following formula that must be revised in your case as well: =IF(COUNT(Uniquer:Uniquer) - 1 < COUNT(DISTINCT({SampleContacts PickState})), "Alert", "")

You can play with what you'll find at those links, and clone as needed into your own space. If in your Uniquer sheet you name your first (primary) column [Uniquer] and your third column [Alert] then you'll simply need to replace the {bolded items} with your own cross-reference source in both formulas.

I have purposely displayed the error condition to illustrate that you'll be alerted in case of failure to include in your Uniquer sheet sufficient sequenced rows to encompass the count of unique conditions you're reporting on. If you edit the sample Uniquer sheet to remove the error flag by adding next-in-sequence values in the [Uniquer] column, please revert and save before closing your inspection session so the next user will see the error.

The plus-sign in the [UniqueStates] column formula forces the error flag to the top in the report (assuming that column is sorted in ascending order). A conditional format displays the error prominently.

Paul Newcome

There are a number of different options, but the one that is right for you depends on what the end goal is for this data.

Lisa Kennedy

The end goal is a list of each entry only once. No repeats of data (names). I have the data both in contact list and text form

Paul Newcome

I understand that part, but are you looking for informational list only, or are you planning on referencing these names for metrics formulas? How and where is the end information going to be displayed?

Example: If you just needed a list to be displayed on a dashboard, you could use a very basic formula in a multi-select dropdown type column, but if you were planning on running complex metrics then you would need to create a sheet, and if you just needed some basic counts/sums/averages for a chart then you could use a grouped report.

Lisa Kennedy

Gotcha Paul, I will be doing the latter, using the names to run metrics Index(Collect based on those unique (non-repeating) names

Paul Newcome

Ok. I am going to suggest doing this on a separate sheet.

In a text/number column I will call "Number" in this example manually enter numbers going down the rows starting with 1 and going down as far as you want to go.

The next column will be where your list goes, so you can make it whatever type of column is appropriate for the incoming data and use this:

=INDEX(DISTINCT({Column Containing Data}), Number@row)

Lisa Kennedy

Late on this but thank you Paul. Always a pleasure to see what solution you can spin up

Cleversheet

Generating a list of unique values in Smartsheet should not be hard to do, nor the guidance for it hard to find. Unfortunately, until now that's not been the case. Here is a solution that I believe will work flawlessly, though of course I welcome critique that will exposes its weaknesses so they can be resolved.

You need three primary elements to pull, in this example, the unique states from a sample list of contacts and display them sorted alphabetically in a report:

• a source sheet (this link is to a public-domain sample contacts listing, and none of the info is believed to refer to real people)
• what I call a uniquer sheet containing this column formula in the [UniqueStates] column, next to the [Uniquer] column that contains sequential numbers starting with zero: 
  • =IFERROR(IF(Uniquer@row > 0, (INDEX(DISTINCT({SampleContacts PickState}), Uniquer@row)), IF(Alert@row = "Alert", "+Need more rows in Uniquer", "")), "")
• the resulting report. Any applicable edit in the source data will ultimately appear in the report. Obviously, this report can be included in a dashboard or wherever else useful to you.

Note that cell Alert1 in the Uniquer sheet contains the following formula that must be revised in your case as well: =IF(COUNT(Uniquer:Uniquer) - 1 < COUNT(DISTINCT({SampleContacts PickState})), "Alert", "")

You can play with what you'll find at those links, and clone as needed into your own space. If in your Uniquer sheet you name your first (primary) column [Uniquer] and your third column [Alert] then you'll simply need to replace the {bolded items} with your own cross-reference source in both formulas.

I have purposely displayed the error condition to illustrate that you'll be alerted in case of failure to include in your Uniquer sheet sufficient sequenced rows to encompass the count of unique conditions you're reporting on. If you edit the sample Uniquer sheet to remove the error flag by adding next-in-sequence values in the [Uniquer] column, please revert and save before closing your inspection session so the next user will see the error.

The plus-sign in the [UniqueStates] column formula forces the error flag to the top in the report (assuming that column is sorted in ascending order). A conditional format displays the error prominently.

Lisa Kennedy

Works beautifully Cleversheet!