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

03/23/21
Accepted

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 PGenevieve P admin
    Accepted 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

  • Accepted 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 PGenevieve P admin
    Accepted 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

  • Accepted 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 -

Sign In or Register to comment.