Compile a list of unique values

Nwood1982

I've been playing around with this all day. I'm trying to figure a way to compile a list all the unique values in a given column (in this case, business entities/customers). I'm matching that against an "Inactive" checkbox and trying to end with a list of all customers with currently active projects. Ideally in a report so I can show it on a dashboard. I don't see how to get my list of uniques.

I can get the customer list into a report, but can't figure out how to filter out the duplicate entries. I suspect I'm coming at this from the wrong angle.

I would really like this to be a dynamic report so that as we add requests to the primary sheet, the customer is added (if not a duplicate) to said report. POSSIBLY with a tally.

Find more posts tagged with

Sheets

Healthcare

Reports

Project Management

Comments

Shaine Greenwood

Hello,

To achieve what you want, you'll want to start with a formula that flags duplicates. I found a great community post where people offer ways of flagging duplicates in a column: https://community.smartsheet.com/discussion/nested-if-statement-flagging-duplicates

You might adapt this to a checkbox column that just checks the box when something is a duplicate. You can create a report based off of which boxes are checked/unchecked.

Nwood1982

This only seems to get me part of the way there. Here is my formula (for reference)

=IF(COUNTIF([Clinic Requesting]:[Clinic Requesting], [Clinic Requesting]1) > 1, 1, 0)

COUNTIF([Clinic Requesting]:[Clinic Requesting], [Clinic Requesting]1)

- returns the number of times the customer is listed in this sheet.

so:

=IF(number of times customer is in sheet > 1, 1, 0)

This correctly marks if a given line as a duplicate customer (i.e. the same customer requesting multiple things).

Now for the however - this doesn't actually get me to the report (so far as I can see). I can run a report and it will either return all of the rows that are marked or none of them (yes or no in my duplicate? flag checkbox).

I want EVERY customer listed only once, regardless of whether or not they've requested multiple things. I hope this makes sense.

Nwood1982

Hah! Found it. Now it only checks for duplicates in the range above the current row.

=IF(COUNTIF([Clinic Requesting]$1:[Clinic Requesting]14, [Clinic Requesting]15) >= 1, 1, 0)

I still have to add in a check against my "active" checkbox, but there it is.

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

Sign in to join the conversation!

Quick Links

Trending discussions

Why is % Complete not the same between Smartsheet and Microsoft Projects
I have two identical projects with the exact same data, but the % Complete in Smartsheet is 14% and the % Complete in Microsoft Projects is 4%. Which one is correct
Report not showing all rows
I have a report that's sorted ascending by name but stops in the "T" section. When I sort it descending by name it shows the missing names after the "T" section at the top but then stops in the "A" section, but not all of them. There are 2217 rows and 19 columns in the report right now, is there a limit to how many rows I…
Create a new Grid outside of WorkSpace
I have not moved into the Workspace feature, I have my sheets all in normal historical folders. Now when I try and create a new Grid, it forces me into a workspace. How do I create a grid outside of a workspace?