cesar.faria96001 ✭
07/12/19 edited 12/09/19 in Formulas and Functions
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.
Tags:
- Formulas
0 · Share on FacebookShare on Twitter
Andreas S. ✭✭
07/19/19
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
See AlsoCOUNTIFS function with multiple criteriaCOUNTIFS function with multiple criteriaCustom Text for SymbolsLink an entire column from 1 sheet to anotherKind regards
Andreas
PS: Sent an invitation to this sheet.
0 · Share on FacebookShare on Twitter
Cleversheet ✭✭✭✭✭✭
07/02/22
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.
0 · Share on FacebookShare on Twitter
Michael Parker ✭✭
07/14/22
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!!!
4 · Share on FacebookShare on Twitter
mjsmith ✭✭
07/20/22 edited 07/20/22
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), "")
1 · Share on FacebookShare on Twitter
Michael Parker ✭✭
07/21/22 edited 07/21/22
mjsmith ✭✭
07/26/22
@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?
1 · Share on FacebookShare on Twitter
RNP ✭
08/05/22
Same here, not able to get unique data from referencing sheet
0 · Share on FacebookShare on Twitter
Cleversheet ✭✭✭✭✭✭
08/05/22
@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.
1 · Share on FacebookShare on Twitter
Ciro Moreno ✭✭
06/21/23
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.
1 · Share on FacebookShare on Twitter
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.7K Get Help
- 376 Global Discussions
- 205 Industry Talk
- 437 Announcements
- 15 Community Corner Newsletter
- 139 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!