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

Edwina Sampler-Wright

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

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 -

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 -

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.