Help Separating Data from a row in One Sheet to Individual Rows in Another
My team updates documents (docs). We have two sheets that we call trackers:
- Document Tracker: Our main sheet tracks which documents are being updated. In one column we have the document title and in another column we list any record #s that are driving the updates to that document.
- Record Tracker: On another sheet we track the individual records associated with updates. Each record has a 6-digit ID (For example: 111111, 123456, 989898, etc.).
We want to automate the Record Tracker to make new record entries as they are added to the Document Tracker, but the problem arises when individual documents having multiple records and also records having multiple documents. I cannot use dropdowns since there are too many records and documents.
Situations:
- Some docs will have no associated records. These items do not need to be in the Record Tracker.
- Some docs will have only 1 associated record. These are easy to work with.
- Some docs have multiple associated records, but there is no max number of records that can be associated to a document.
Here is a made up example of the two sheets:
- I can setup a Data Mesh to add new entries to the Record Tracker, but it sees entries with multiple Record #s as their own entries and not individual records.
- I can also set up a formula on the Record Tracker to combine all document titles from the document tracker that are associated to a given record #, but it will only populate documents that are being updated for the one record, not items that have multiple record #s.
Is there a way to automate separating the Record #s from the Document Tracker as individual entries in the Record Tracker with their associated document titles?
Best Answer
-
For some reason that only listed the documents that had multiple Record #s yet contained the desired Record # (For example: for 111111 only SOP-004 would show).
My previous formula would only pull items with one Record # listed (For example: for 111111 only SOP-001 & SOP-0003 would show)
Since they were two halves of the whole, I was able tweak and combine it with the formula I already had so that strangely worked.
This is what I ended up using (but with CHAR(10) instead of "; "):
=JOIN(DISTINCT(COLLECT({Doc Tracker – Doc Column}, {Doc Tracker – Record Column}, CONTAINS([Record#]@row, @cell))), CHAR(10) + CHAR(10) + JOIN(DISTINCT(COLLECT({Doc Tracker – Doc Column}, {Doc Tracker – Record Column}, [Record#]@row)), CHAR(10))Thank you!!!
Answers
-
I would suggest the formula route.
=JOIN(COLLECT({Doc Tracker - Doc Column}, {Doc Tracker - Record Column}, CONTAINS([Record #], @cell)), "; ")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
For some reason that only listed the documents that had multiple Record #s yet contained the desired Record # (For example: for 111111 only SOP-004 would show).
My previous formula would only pull items with one Record # listed (For example: for 111111 only SOP-001 & SOP-0003 would show)
Since they were two halves of the whole, I was able tweak and combine it with the formula I already had so that strangely worked.
This is what I ended up using (but with CHAR(10) instead of "; "):
=JOIN(DISTINCT(COLLECT({Doc Tracker – Doc Column}, {Doc Tracker – Record Column}, CONTAINS([Record#]@row, @cell))), CHAR(10) + CHAR(10) + JOIN(DISTINCT(COLLECT({Doc Tracker – Doc Column}, {Doc Tracker – Record Column}, [Record#]@row)), CHAR(10))Thank you!!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!