Avoiding Duplications when Copying a Row with Workflow Automation

Gwynneth

Hi,

I have mastered Copying a Row with the Workflow Automation function. I have built reports off of the Destination sheet. However, I will need to update those rows from time to time. How do I do that without duplicating the same information? Can I write a workflow to overwrite the existing information in the destination sheet?

Gwynneth

Neil Watson

@Gwynneth if you have a column where there is a unique identifier to differentiate rows, then you can use one of the methods described in the post below. Basically, when the updated row is copied to the destination sheet, the formulae identify (for the now 2 duplicate rows) which is the older (not updated version). You can then use automation to move the old row to a 3rd archive sheet.

https://community.smartsheet.com/discussion/comment/268713#Comment_268713

I havc used this solution a few times:

Let me know if you need any assistance to set this up.

Neil Watson

Hi @Gwynneth are you updating the rows in the source sheet, or the destination sheet? I assume source sheet, but wanted to be sure.

Gwynneth

Hi @Neil Watson , I am updating the rows in the source sheet.

Neil Watson

@Gwynneth if you have a column where there is a unique identifier to differentiate rows, then you can use one of the methods described in the post below. Basically, when the updated row is copied to the destination sheet, the formulae identify (for the now 2 duplicate rows) which is the older (not updated version). You can then use automation to move the old row to a 3rd archive sheet.

https://community.smartsheet.com/discussion/comment/268713#Comment_268713

I havc used this solution a few times:

Let me know if you need any assistance to set this up.

Dell

@Neil Watson I am having this issue and I am trying to use your solution but I am running into some problems...

1) do you add these three columns in the source sheet or the destination sheet?

2) I am getting am #UNPARSEABLE error when I use the "IS_OLD" formula, did Smartsheet update? If so, can you please provide the updated formula

This will be incredibly helpful!!

Thanks

Genevieve P.

Hi @Dell

1) The formulas & columns are in the source sheet

2) Can you post a screen capture of your current sheet with column names, and post the formula you've tried? The formula would need to be changed based on your columns.

Dell

@GenevieveB - thanks for your response, see screenshot below:

I am trying to create an automation where if the user clicks the check box, then the item will move to a different sheet (I am using this same automation across multiple sheets). The goal is so create one master that are approved but enable teams to continue editing the source and have the two linked. Once the automation runs, I am hoping to update the items vs. duplicating them. Please let me know what you think.

Genevieve P.

Hi @Dell

My apologies for the delay! Do you have a column titled "Product#" in your sheet? This would be the unique identifier that's linking your rows together.

Adjust the references in the formula to look at your current column names... and the ROW# will need to have [these square brackets] around it as well.

Try something like this:

=IF(AND(COUNTIF([Unique Column]:[Unique Column], [Unique Column]@row) >1, MATCH([Unique Column]@row, [Unique Column]:[Unique Column], 0) = [ROW#]@row),1,0)

Hannah_Trousdale45

@Genevieve P. I have set up the three columns in my Master Sheet (Source) and I would like to figure out what type of automation I need to make in my destination sheet to move my old rows into my archive sheet. I would like to be able to edit rows in my master sheet and have them populate into my Test sheet without creating multiple rows after each edit.

Genevieve P.

Hi @Hannah_Trousdale45

I would personally suggest using a Report to surface those 3 columns instead of a separate sheet. That way you can update data directly in the Report and it will update the underlying sheet (and vice versa).

• Moving rows will take them out of your current sheet and move them fully over to the next one.
• You can Copy Rows to another sheet, but then that copy is like a copy/paste, meaning it's static information and does not update as you make updates to the source.

DataMesh may be an option if you're looking to add cell links to your destination sheet, so the data updates in both places, but keep in mind that you would need a unique identifier on the row so it doesn't create those duplicates you mention.

If none of this helps, I would recommend creating a new Question post here in the Community with screen captures showing your set-up (blocking out sensitive data) and explaining exactly how you want the destination sheet to look like.

Thanks!
Genevieve

Hannah_Trousdale45

@Genevieve P. Thank you!!