How do I prevent rows from being copied across if the target sheet already shares a cell value?

crischadikun

I am trying to create a system to track changes to cells. For example, if an order gets changed, I have automation set up to copy this row to another sheet. This way, I have a separate sheet with all the changed orders, and can then inform customers of delays.

The problem I am facing is that if the same order gets changed twice in the master sheet, then it will copy the row (with the same order number) twice into the new sheet. Is there a way for A. only the changed rows to get copied across, and B. if they do get copied across, there are no duplicate rows with identical order numbers. Do i need to use datamesh? If so, how?

Ultimately once this new sheet is working correctly, I will create reports which get sent to customers regularly to inform them of delays to orders relevant to them.

Genevieve P.

Hi @crischadikun

Thank you for clarifying! In this case, I would actually suggest only using a Report instead of using a second sheet with copied rows. Copied rows are static, as you've found, so as the sheet updates again it can only create duplicate rows versus updating a current row in the other sheet.

However Reports are a sort of window into the source sheet, so as information changes in the sheet those rows will automatically update in the Report (and vice-versa: you can change information in the Report and it will update the sheet).

Instead of a Copy Row automation, what about using the Record a Date action in a helper column to identify when/if an adjustment is made. Then you can Filter by this helper column in your Report so only rows that have been changed will show up. This way if they change again, the data showing in the Report will be current and you won't have any historical/copied data.

Would that work for you?

Cheers,

Genevieve

Genevieve P.

Hi @crischadikun

Can I clarify, in the instance where the same order is changed again, do you want the new information to replace the previously copied row, or do you want to prevent this next change from copying at all?

If you only need the copy to happen once, I would do the following:

1) Set up a Helper Column in your current sheet (Sheet A) that uses a cross-sheet formula to look for the order number in Sheet B (where the copied rows go).

If there are 1+ rows in the other sheet, it will check a box.

Ex: =IF(COUNTIF({Order Number Sheet B}, [Order Number]@row) > 0, 1, 0)

2) Then in your workflow, set up a Condition Block to only copy the row over if the box is not checked (or this is the first time the order has been changed).

See: Condition Blocks: Filter What Your Automated Workflows Send

Let me know if this will work for your process!

Cheers,

Genevieve

crischadikun

@Genevieve P. I would need the new information to replace the previously copied row to ensure all dates are current.

Genevieve P.

Hi @crischadikun

Thank you for clarifying! In this case, I would actually suggest only using a Report instead of using a second sheet with copied rows. Copied rows are static, as you've found, so as the sheet updates again it can only create duplicate rows versus updating a current row in the other sheet.

However Reports are a sort of window into the source sheet, so as information changes in the sheet those rows will automatically update in the Report (and vice-versa: you can change information in the Report and it will update the sheet).

Instead of a Copy Row automation, what about using the Record a Date action in a helper column to identify when/if an adjustment is made. Then you can Filter by this helper column in your Report so only rows that have been changed will show up. This way if they change again, the data showing in the Report will be current and you won't have any historical/copied data.

Would that work for you?

Cheers,

Genevieve