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.

get-distinct-values-excel.png

TESTE.PNG

Tags:

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]3

    Kind regards

    Andreas

    PS: Sent an invitation to this sheet.

    bff769bc4ec7d4c0ae52073cffb740b3.jpg

  • Cleversheet
    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 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, 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.

  • 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!!!

  • mjsmith
    mjsmith ✭✭
    edited 07/20/22

    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), "")

  • Michael Parker
    edited 07/21/22

    @mjsmith

    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), "")



  • mjsmith
    mjsmith ✭✭

    @Michael Parker

    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

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭

    @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 cross-reference 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 self-manage 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!