Help Separating Data from a row in One Sheet to Individual Rows in Another

Allison.Saettele

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?

Allison.Saettele

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

Paul Newcome

I would suggest the formula route.

=JOIN(COLLECT({Doc Tracker - Doc Column}, {Doc Tracker - Record Column}, CONTAINS([Record #], @cell)), "; ")

Allison.Saettele

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

Paul Newcome

Happy to help. 👍️