Generate List of Unique Values - INDEX? DISTINCT? VLOOKUP?

Leibel S

@Clark Coles You can technically make a 'helper sheet' that checks if the qty in your range is more than the rows on your sheet and if yes it uses copy row to add a row to your sheet...

Matt Stewart

@Paul Newcome @Leibel Shuchat .. im trying to get this working for myself and running into a few questions.

Essentially we have a master sheet with lots of duplicates for various orders. We add new and duplicate items here all the time.

I want to pull all unique items into a separate sheet (not a report) so i can use card view and let team update progress on original items. I want the new original items added to master sheet to auto populate in this new design sheet.

Then the master sheet can use a index/match to pull back any design updates back into master sheet in all duplicate and original items for visibility.

QUESTIONS:

1. Is it possible to do this without adding the Helper column to the master sheet? Or can i do it in a way that sorting does not affect it? NOTE: i can NOT do the "put everything under a parent row" trick, as that will screw up way too many formulas and reports.
2. Is there a way to filter the helper column to only assign a number if it is unique AND is not a parent? Design team does not work on parents and only needs children or childless items.

Tummi

Is there a way for you to be able to write a COUNTIF function after going through this whole process? The COUNTIF function would be on the Unique names sheet on a separate column.

Tummi

@Paul Newcome Is there a way for me to build out a COUNTIF Function after going through the process? The steps you showed me were successful now I'd like to add an additional column to build out the counts on the Unique names field if that makes sense.

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