Need to reference distinct values from another sheet

I'm looking for a formula to automatically pull distinct values from a column in another sheet to use in a reference sheet.

Example:

Source sheet 1 has a list of [CustomerID] and various other columns. Some [CustomerID]'s are listed on multiple rows.

Reference sheet 1 wants to list all the DISTINCT([CustomerID]) values, so that I can pull metrics on the distinct customers. I want this list to be a formula or automated so that I (nor anyone managing this sheet in the future) has to copy and past the column. Meaning if the [CustomerID] list in the Source Sheet 1 changes, then so does the DISTINCT([CustomerID]) list in the reference sheet.

thoughts?

Find more posts tagged with

Formulas

Automations

columns

References

Accepted answers

All comments

Jgorsich

Presuming you've got a Row column (Is there a way to reference the row above? — Smartsheet Community), you can just do:

=index(distinct({customerID}),row@row)

I've not tested it, so you MIGHT need to do a distinct(collect({customerID},{customerID},<>"")) but I don't think you need to.

Erin Horiuchi Green

Hello @BSReid24 ,

I am sure there is a more elegant, sustainable solution.

But, just to get from point A to point B, have you tried using Pivot?

Pivot App Learning Track

Example CustomerID Column

Example Pivot Setup

Pivot does not update — Smartsheet Community

BSReid24

@Jgorsich the source sheet will be used as a template (of sorts and reused) so I was worried the row number may cause issues. What I mean, the actual sheet is tracking interactions with "customers" during natural disasters. So I am setting up this system of sheets (source and reference sheets) that feed to a dashboard. They will document the information then "save as new" after they finish documenting, clear the source sheet, and use it again for the next natural disaster/emergency. We don't want to reinvent the wheel every time or make it complicated for team members entering information that are not as familiar with the "behind the scenes" of Smartsheets. I was tasked with building one dashboard even though this changes. Im wondering if creating a folder of all the files and use it as a template, then copy all of them as new under a new natural disaster/emergency folder with a new dashboard… but that may eventually get to be too many dashboards.

Not a simple request or task. I am still trying to figure out the best way to do it, that is reasonable and manageable.

@Erin Horiuchi Green I am not familiar but I will check out Pivot.

Jgorsich

Gotchya.

Seems like the solution you might want then is to make a form that the team member will fill out, and when they click "submit" or "save and close" populates a sheet with what they recorded that they never need to see or look at again. Then you can process data from that sheet and collapse various rows generated by their use of the form down to a single line in your reference sheet.

BSReid24

There is an external report that they run daily during natural disasters/emergencies. It produces a table in a spreadsheet. I made a Smartsheet where they could copy and paste the table into the Smartsheet in the same format. Is there a way for them to upload data to a Smartsheet (multiple rows) another way? The only other way I know is utilizing another software to feed a dashboard from a database/server.

Jgorsich

Check the Automation function (up next to the File menu) on the sheet you want to wipe out. You can set it to move the cells to another page and clear the cells on your primary page at an applicable trigger. For YOUR purposes you really want a long growing list of the actions preserved - that way if a single issue spans more than one day you can tie it to the appropriate record and get a history of it, etc. For THEIR purposes you want an empty input sheet. If you automate copying the data to your intermediary sheet (between the Primary and the Secondary you were already considering), you can have the best of both worlds.

Erin Horiuchi Green

Hello @BSReid24,

When I have data from a 3rd party source that I need to pull in, then I do utilize the below data strategies.

Access to 3rd Party Source API

1) Python script pulls targeted data from a 3rd party source via an API into a CSV on SharePoint. Nightly refresh overwrite.
- SharePoint CSV has change history log.

2) Data Shuttle uploads CSV to Data Table with unique filtering and record id properties.
- This is not necessary. Personally, I use it to increase easy access for audit trail transparency.
- Smartsheet API does not have the ability to download the Smartsheet Activity Log.
- Smartsheet Activity Log only allows collaborator designation search entry; no other search text allowed.

3) Data Table connection to Smartsheet.

Daily Email with XLSX Report

1) PowerAutomate captures daily email XLSX attachment and overwrites XLSX on SharePoint.
- PowerAutomate has change history log.
- SharePoint has change history log.

2) Data Shuttle uploads CSV to Data Table with unique filtering and record id properties.
- This is not necessary. Personally, I use it to increase easy access for audit trail transparency.
- Smartsheet API does not have the ability to download the Smartsheet Activity Log.
- Smartsheet Activity Log only allows collaborator designation search entry; no other search text allowed.

3) Data Table connection to Smartsheet.

@Jgorsich I love Automation, but, I have noticed that regardless of introducing helper columns (locked/hidden) there is Automation I am left wanting. I have hit the below limitations.

1) Smartsheet limitation of 500,000 cells using Copy To Automation when Rows Added or Changed for an Archive.

2) No more than 5 unique Automation Workflows can run concurrently.

Jgorsich

@Erin Horiuchi Green I totally agree that this is a situation where going to data shuttle and API coding and python coding may be the best long term answer - I'm typically hesitant to recommend it though due to both the complexity (and number of things that can go wrong if executed on a machine outside of your control) and the fact that it just rubs me wrong to have to use a multi-step workaround for something that should be able to be handled natively inside of Smartsheet.

That being said - @BSReid24, if you are up to the technical challenge, Erin's answer will likely end up being cleaner than mine. Mine will work (as long as you aren't trying to move more than 500,000 cells at a time), but does require manual pasting of the data into your blank Primary Smartsheet to start. Her solution can likely pull from the original data directly and skip that step, which I'm sure would be appreciated by everyone concerned.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.