Extracting a list of unique values from a list on another sheet

Paul G. · 04/08/22

In your response on 07/08/20 to Niyati Kumar you indicated "Take a look at THIS SHEET. It provides a solution for parsing a list of unique names from another sheet without needing the helper columns on the source sheet." When selecting THIS SHEET it is no longer published. Would it be possible for you to publish this sheet again? I am looking for a solution to a similar situation.

Thank you.

Paul G.

Brian Becnel · 06/09/22

@Paul Newcome Agree with @Paul G. , this thread reads as though it would solve my week-long struggle to find a solution WITHOUT going through the hoops of using Pivot and Shuttle. Would you be able to re-publish your help sheet referenced?

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.

SunnyJ · 07/26/22

Can someone please republish 'THIS SHEET' which seems to negate the need for a unique column?

Paul Newcome · 07/26/22

@Paul G., @Brian Becnel, and @SunnyJ,

My apologies for the delayed response. I missed the earlier notifications. The formula to grab the list of unique names would be something along the lines of...

=INDEX(DISTINCT({Source Sheet Name Column}), 1)

You would change the 1 to a 2 to get the second unique name then to a 3 to get the third, so on and so forth.

MazU · 08/26/22

https://community.smartsheet.com/discussion/comment/196336#Comment_196336

@Paul Newcome

Hi Paul, I created something similar to but only upto the first screenshot.

However, the numbers it is generating is not in sequence eg please see my screenshot

Please can you advise

Cleversheet · 08/26/22

@Maz Uddin , you’ll need to either perform a manual sort on that column within your Sheet, or else create a Report from that sheet and sort the column there. There’s no other way for the system to know you care about the sequence that the unique values found elsewhere are displayed.

Paul Newcome · 08/26/22

@Maz Uddin You do not need to sort anything or create any reports. That is actually expected. That is why we use the SMALL function in the next step. You are not matching on the exact numbers in the source sheet. You are pulling the first smallest then the second smallest then the third smallest, so on and so forth which is what is happening in the second screenshot.

MazU · 08/26/22

@Paul Newcome I just did the whole thing together and it's working. Thank you.

Paul Newcome · 08/26/22

@Maz Uddin Happy to help. 👍️

MazU · 09/01/22

@Paul Newcome Hi Paul,

I am facing a issue here with this formula

the names are appearing more than once

can you please advise

Paul Newcome · 09/01/22

@Maz Uddin What do you have in Column 2?

john I · 01/12/23

@Paul Newcome

I was looking to find solution to this topic {extract unique values from one sheet to another} without need of a helper column. I see that you posted a link to a sheet; however, it looks like it was removed. Do you still have this or can direct me accordingly?

I have a source smartsheet with a column ("name") and attempting to identify all unique values into a new target smartsheet in which i summarize data from the source sheet in the target sheet using "name" (e.g. counts, sumifs, etc). Thanks

Paul G. · 01/12/23

@john I on 7/26 @Paul Newcome responded to several of us who also inquired with the following suggested formula.

=INDEX(DISTINCT({Source Sheet Name Column}), 1)

Hope that helps.

Paul Newcome · 01/12/23

https://community.smartsheet.com/discussion/comment/337481#Comment_337481

@john I Here is the post that @Paul G. is referring to.

Extracting a list of unique values from a list on another sheet

Comments

Categories