How to automatically remove "unmatched" datamesh results from the Target sheet?

Hi Smartsheet SMEs! In DataMesh, selecting the "Copy and Add Data" option will keep the Source and Target sheet in sync --- automatically creates new lookup values if needed. Have you devised a solution that automatically removes/deletes unmatched data --- rows in the target sheet that no longer reconciles to the Source sheet?

Thanks in advance for knowledge sharing!

Edwina Sampler-Wright, PMP

Find more posts tagged with

API and Developers

Integrations

Product Development

Project Management

Enhancement Request

Accepted answers

Genevieve P.

Hi @Edwina Sampler-Wright

Data Mesh currently cannot delete out unmatched data in the target sheet, as you've found.

The way I would quickly identify rows in the target sheet that no longer have a match would be to set up a cross-sheet formula to check a box if the value is found on both sheets. Then you could set up conditional formatting to highlight rows in the target sheet that have this box checked.

Ex:

=IF(COUNTIF({Column with unique value}, [Unique Value]@row) >= 1, 0, 1)

Let me know if you'd like to see screen captures of what I'm describing and I'm happy to clarify further!

Cheers,

Genevieve

Edwina Sampler-Wright

Hi @Genevieve P , thanks so much for the suggestion. This a great workaround. Using a Checkbox column property, I amended the formula as follows --- IF(COUNTIF({Project Number Range}, [Project Number]@row) = True, True, False) and voilà!

Thanks again for your help.

Edwina -

All comments

Genevieve P.

Hi @Edwina Sampler-Wright

Data Mesh currently cannot delete out unmatched data in the target sheet, as you've found.

Ex:

=IF(COUNTIF({Column with unique value}, [Unique Value]@row) >= 1, 0, 1)

Let me know if you'd like to see screen captures of what I'm describing and I'm happy to clarify further!

Cheers,

Genevieve

Edwina Sampler-Wright

Thanks again for your help.

Edwina -

Samuel Mueller

@Genevieve P. Is this feature to delete records on the roadmap for datamesh? having to create cross sheet references and lookup formulas manually to know when a source row has been deleted is a huge gap in datamesh functionality.

Sara H

@Genevieve P. Not sure if this process has changed since 2021, but if it hasn't, is the Column with unique value the cross-sheet reference?

Genevieve P.

Hi @Sarah H

The column with a unique value is any column on your sheet that identifies your row as a unique row. For example, a Name or a row ID. In the example above, it was a Project Number. That way the formula could look to see if the number exists in both sheets or not.

Cheers,

Genevieve

robinsirohi

Hi @Genevieve P.

How can we use this formula

IF(COUNTIF({Project Number Range}, [Project Number]@row) = True, True,False) if we have to compare it from more than 1 sheet.

Genevieve P.

Hi @robinsirohi

This depends on what it is you're looking to do!

The easiest thing here would be to set up multiple COUNTIF statements and add them together:

COUNTIF({Project Number Sheet 1}, [Project Number]@row) + COUNTIF({Project Number Sheet 2}, [Project Number]@row) + COUNTIF({Project Number Sheet 3}, [Project Number]@row)

And so on.

This will then return a COUNT of how many times that Project Number appears across sheets 1, 2, and 3. Then your IF statement can look at those numbers added together to complete your instructions:

=IF( (Countif(1) + Countif(2) + Countif(1)) = XXX, then do YYY)

Does that make sense?

Genevieve

Samuel Mueller

I've heard you can use the pivot app, but I have not tried this solution.

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.