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.

Find more posts tagged with

Formulas

Comments

Andreas S.

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.

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.

Michael Parker

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

https://community.smartsheet.com/discussion/comment/336059#Comment_336059

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

@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

@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?

RNP

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

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.

Ciro Moreno

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?