Get a list of Distinct / Unique values
Hello, Gentlemen!
Here is the situation: I need to get a list of distinct/unique values in a column using a formula on Smartsheet. I know how to create this formula on excel, but when i convert to Smartsheet, it simply doesn't work.
The formula that I used in Excel is the following:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")
The formula converted to Smartsheet is the following:
=IFERROR(INDEX($[Column6]$2:$[Column6]$9, MATCH(0, INDEX(COUNTIF($[Column7]$1:[Column7]1, $[Column6]$2:$[Column6]$9), 0, 0), 0)), "")
Could you guys help me with this one? The name just repeats, I have tried inumerous things, but i don't know what it is.
Comments

Hi Cesar,
SmartSheet is not Excel, but I did it. It´s not pretty  but seems to work.
The screen shot is attached and here are the formulars:
[Spalte2]1=Name1
[Spalte3]1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JOIN([Spalte2]:[Spalte2]; "#"); "##"; "#"); "##"; "#"); "##"; "#")
[Spalte4]1=LEFT([Spalte3]1; FIND("#"; [Spalte3]1)  1)[Spalte2]2=IF(Name2 <> [Spalte2]1; Name2)
[Spalte3]2=SUBSTITUTE(MID([Spalte3]1; LEN([Spalte4]1) + 2; 9999); "##"; "#")
[Spalte4]2=IF(FIND("#"; [Spalte3]2) > 0; LEFT([Spalte3]2; FIND("#"; [Spalte3]2)  1))[Spalte2]3=IF(COUNTIF([Spalte2]$1:[Spalte2]2; =Name3) = 0; Name3)
[Spalte3]3=SUBSTITUTE(MID([Spalte3]2; LEN([Spalte4]2) + 2; 9999); "##"; "#")
[Spalte4]3=IF(FIND("#"; [Spalte3]3) > 0; LEFT([Spalte3]3; FIND("#"; [Spalte3]3)  1))
Enter your names in [Name] and pull down [Spalte2]3 [Spalte3]3 [Spalte4]3Kind regards
AndreasPS: Sent an invitation to this sheet.

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 publicdomain 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 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 crossreference 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, please revert and save before closing your inspection session so the next user will see the error.
The plussign 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.

Nice to see the different options.
If your sheet has a unique Row Number column, you can solve this problem in one line of code:
=IFERROR(INDEX(DISTINCT([Primary Column]:[Primary Column]), [Row ID]@row, 1), "")
Distinct(Column: Column) returns the range of distinct values, but must be used with another function.
Index( gets the distinct range, selects the row using our Row Number Column, and selects column 1).
The IFERROR() function allows us to clear the cells that do not have a valid value.
Hope this helps!!!

This is brilliant! I am trying to implement this formula referencing another sheet and it is saying #Unparseable. Any ideas why?
=IFERROR(INDEX(DISTINCT({Name}:{Name}), {Row ID}@row, 1), "")

Change the curly brackets to square brackets and it should work. If the field name has no spaces you could get away with dropping the bracket altogether.
=IFERROR(INDEX(DISTINCT(Name:Name), [Row ID]@row, 1), "")

Actually, I am trying to create a list of unique names on a new sheet, separate from where the list of names resides.
Sheet with names:
New Sheet, referencing sheet with Column Name above.
As you can see in the first sheet I do have your original formula working perfectly. What I am trying to do is get that list on a separate sheet so I can build some metrics for the unique list of users. My first attempt was to take your formula and reference the other sheet but that resulted in an error. Do you know if there is a way to make that list appear on the other sheet?

Same here, not able to get unique data from referencing sheet

@mjsmith (and @RNP—and @Michael Parker), in your new sheet I don't see the column of numbers (1,2,3,...) that need to be there for the Unique formula set to do its thang.
If you refer back to my 7/2/22 post and ensure that you're building your solution in a way that tracks mine exactly, there's no reason you should not end up with a Uniquer sheet (or whatever you want to call it) that contains a list of unique values from the selected column in your source sheet. As you can see by inspecting both my Uniquer sheet and its resulting report, the process is working in live mode. There could be some small thing you're overlooking, like not including your revised column name in the formulas. Also, with crossreference formulas you typically have to regenerate them each time; you can't just keyboard the revised names.
Note that my solution has a starting zero in the [Uniquer]1 cell. That's to enable the Alert mechanism to work. If you want to selfmanage the number sequence in that column you can remove row 1 and the [Alert] column completely.

Hello, I was able to pull the unique list referencing to another sheet using @Michael Parker solution.
it's very simple, in the formula just replace [Primary Column]:[Primary Column] by selecting the referenced column in the formula module.
=IFERROR(INDEX(DISTINCT({Names Column Range 2}), [Row ID]@row, 1), "")
you do need to have the Row ID column with the 123... number sequence on the new sheet as @Cleversheet said.
Help Article Resources
Categories
Check out the Formula Handbook template!