Dynamic list of unique IDs from 5 sheets

Hi dear Smartsheet experts.

We have 5 sheets with different lists of lines, key is "Site ID", let's call it ID. Sheets have different number of lines and duplicated IDs inside. Sheets are alive: lines come in and go out daily.

Our goal is to have a separate sheet ("Unique IDs") with a dynamic list of unique IDs: only unique IDs from all 5 sheets and refresh on a daily (or more) basis, method is clear all and insert new set of actual IDs.

Do you have an idea how to do it in a elegant way and without manual operations? Please share your thoughts.

Current solution: 1) Report from 5 sheets = list of all actual IDs with duplicates, 2) Manual export of Excel from this Report, refresh of intermediate sheet (via DataShutlle) with formula to identify unique IDs, 3) DataShuttle takes only unique IDs base of formula outcome and copies it to Unique IDs sheet

I don't like step 1 with manual manipulation… maybe I overcomplicate and you have a better approach?

Thanks in advance

Find more posts tagged with

Accepted answers

All comments

NicoLHC

@Stanislav Morozov i have an idea , and question why there are 5 sheets ? With one single sheet we could use automatic id and avoid double names . If we can not avoid you could automatically write every new number to a separate sheet and then circle back with index match to check if the number is unique… in your report you can filter out . Do you know what I mean? Otherwise let’s have a short call this morning . Send me a mail to Nico.roepnack@lighthouseconsultings.com

Stanislav Morozov

Hi Nico and thanks for your reaction!

5 sheers are needed to work with different lists of active Site IDs (and different groups of users) from different phases of supply chain process. Some flows are parallel and we manage it separately: in one sheet we work with early orders and clean up data to filter only orders to be processed on next phases, in other sheet we manage warehouse process to identify orders where we have enough material to proceed with shipment etc. So, our goal - is to collect all active Site IDs into one list.

I need to explain one more important detail for the full picture: our Sites Data Base is 4000 Site ID's and grows. We faced with a limitation of Smartsheet to connect our 5 sheets and other sheets with Sites Data Base using INDEX MATCH or DataMesh. So, we decided to cut our DataBase in two (and more in future), create a dynamic list of only active Site ID's (around 1000) and use INDEX MATCH to take necessary fields from list of Data Bases.

If I copy every new Site ID in my accumulated sheet, I need to create a process do regularly clean up not active Site IDs from it. Do you have a good practice to do so?

Thanks for your help and ideas and looking forward to continue our conversation!

Stanislav Morozov

And interesting question… you say "you could automatically write every new number to a separate sheet "… and I don't know how… I know how to copy entire line using automation… but how to copy a particular cell?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.