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

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    Answer ✓

    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 -

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    Answer ✓

    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 -

  • Samuel Mueller
    Samuel Mueller Overachievers

    @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
    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.
    Genevieve P. Employee Admin

    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