List all unique values from a column within one Smartsheet in a separate Smartsheet

GTN

Hi All,

In my Smartsheet I have a column for Workstream - each row (deliverable) in the Smartsheet has a Workstream - there could be one or numerous Workstreams.

In a seperate Smartsheet, I want to be able to pull all Unique values from the Workstream Smartsheet. In Excel I would simply do something like -

=UNIQUE(Sheet1!A:A)

How do I do this in Smartsheet?, I've tried

=DISTINCT({mywortksheet.. Range 2})

Thanks

Genevieve P.

Hi @GTN

What's the purpose of this second sheet, could you use a Report instead?

If you create a Report from the source sheet, you could then use the Group feature to Group by Workstream to identify each unique value. You can then also apply some Summary values based on this Group (ex. counting how many rows/deliverables are associated with each Workstream). See: Redesigned Reports with Grouping and Summary Functions

The DISTINCT Function would need to be used within another function. For example, you can use COUNT(DISTINCT to return a number which is the count of how many distinct values there are, or JOIN(DISTINCT(COLLECT to join together the text values into one cell, but it wouldn't parse out the values into different rows.

Let me know if this helps!

Cheers,

Genevieve

GTN

Hi @Genevieve P.

Thanks for your feedback, what I'm trying to do is build a pivot table that on be represented on the dashboard, which details the count of deliverables (not milestones or summary/role up deliverables) by Workstream and Task Status. for instance you could have workstreams of -

Project Management, Comms, Legal

Status would be

Yet to start, Not started, in progress, Past due, Complete.

The formula for pulling the Count of tasks per Workstream & Status works great, I'm just keen to add the Workstream dynamically (if possible) should a new Worrkstream be added to the plan.

Ultimately I'm looking to represent the data in a bar chart on the dashboard.

Thank you

Genevieve P.

Hi @GTN

Thank you for clarifying!

There currently isn't a way to dynamically add the workstream as a separate, new row into your metric sheet through a formula.

What I would suggest doing is use the JOIN(DISTINCT( to bring together all your workstream names into one cell, using a Multi-Select column. Then you can use COUNTM to count how many workstreams there are, and Alert you if either the number changes or doesn't match your current set-up.

Ex:

=JOIN(DISTINCT({Workstreams}), CHAR(10))

and

=COUNTM([Multiselect Cell]@row)

Cheers,

Genevieve

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

Danielle Arteaga

@Cleversheet - this is phenomenal workaround. I found this answer looking for something else and immediately put it to use. Thank you!

djsheets

@Cleversheet I'm struggling to apply this solution to my sheets. I suspect I am not properly specifying my own cross-reference to my data sheet. My uniquer sheet has the primary column named Uniquer, the third column is named Alert. Per your instructions, I need to replace the {bolded items} with my own cross-reference source in both formulas. My source sheet is named PE-TalentV3. The column that has the unique values I want to list is named Title. I replaced the bold items with {PE-TalentV3 PickTitle} . This does not work, no values show up in the second column of my uniquer sheet.

I appreciate any guidance you could offer.

Cleversheet

@djsheets, unfortunately I can't go back and edit the 7/2/22 message now, but wish I'd included clarification that you can't simply replace {SampleContacts PickState} with {PE-TalentV3 PickTitle} by changing the text between those braces.

Rather, you'll need to select {SampleContacts PickState} in the formula (or simply be properly positioned within it such that the result gets placed there) and then use the Reference Another Sheet link that appears in formula-building mode to select the applicable source sheet and specify the column you want to draw the unique values from.

djsheets

@Cleversheet Thank you for the clarification (and the prompt reply), that solved it!

ADS0001

@Cleversheet I have the Uniquer sheet setup and working correctly, I'm just trying now to figure out how you did the Report (UniqueContactStatesOnly). How did you get the unique count to show for each state? I tried making a summary of =COUNT(DISTINCT(COLLECT(userType:userType))) as well as a =Count(Distinct... and just Count. But I can not get a report that gives me the data the way yours does.

Any help would be much appreciated.

Thanks

Cleversheet

Hi, @ADS0001. I think there's a little misunderstanding here. The shaded column in UniqueContactStates is not a count of the unique values in the underlying sheet associated with each state, but rather is simply the sequential number by which the state appears in the SampleContacts sheet. In other words, coincidentally the first person listed in SampleContacts happens to [imaginarily] live in Louisiana, and thus LA shows "1" in the [Uniquer] column. It would be possible to add another column that counts the number of instances a given state appears in SampleContacts. If that's what you're looking for, tap me again and I'll show you how I'd tackle that one with a COUNTFS formula.

ADS0001

@Cleversheet thanks for clarifying. What I'm looking to do is take two columns from my data set, let's say "userID" and "location", and get an accurate number of "userID"'s from any location. Complicating this is that each "userID" can have many "charges" which necessitates the userID being repeated in the column.

In Excel, I would create a pivot table with "location" as my Row and a Distinct Count of 'userID" as my Value. I'm just not sure how to replicate this in SmartSheet.

Thanks Again

Cleversheet

@ADS0001, in the Uniquer sheet you created that lists all of the unique locations from the transactions sheet, simply add a column that contains a formula similar to this one in the column I added to Contact States UNIQUER to demonstrate this capability:

=IF(ISBLANK(UniqueStates@row), "", (COUNTIF({SampleContacts PickState}, UniqueStates@row)))

My SampleContacts sheet has multiple entries per state, which seems similar to your situation where you have multiple transactions per "UserID". Does that meet the need?

PS - In my 12/17 message I referenced COUNTIFS. Turns out COUNTIF works instead. Note that as of today the Help page for COUNTIF is broken (same result trying two browsers, two devices and two connection methods); I've reported that to Support so hopefully it will be fixed before you try to access it.

Genevieve P.

Jumping in here regarding the Help Article link - @Cleversheet thank you for reporting this! We're investigating why this may be happening and should get the page up and running again soon.

ADS0001

@Cleversheet This seems to solve what I am looking for. Thanks for the help!