Automatically remove a row with the same "ID" as another row

Archie

I've got a master sheet and when certain criteria is met, it copies a row to a published sheet that is shared to some clients for review. however, when new attachments or comments are added to that particular item on the master sheet, they obviously dont show on the published version…

1. I can set up a new automation to copy the updated row to the published sheet. However is there a way I can get the previous row to be moved or hidden/deleted with automation or formulas?
2. Is it possible to create a 'lookup' formula that can pull in attachments from another sheet with the same row ID?

or any other work-arounds…?

Mark.poole

Of Course I use a similar work around with one of the projects I use. Copied rows are always added to the bottom of the sheet. This is a Sequential count for each individual ID and you would just remove once the count goes to 2.

=COUNTIFS(Date:Date, >=Date@row, [ID of Welder/Fitter]:[ID of Welder/Fitter], =[ID of Welder/Fitter]@row))

There is also this formula option. "The one I use that just flags a row with a 1. and removes the 0 with automation)

=IF([Date Created]@row = MAX(COLLECT([Date Created]:[Date Created], [Asset ID]:[Asset ID], =[Asset ID]@row)), 1, 0)

Date is the auto number im using (System created date) , Asset ID is my unique id for the row

Mark.poole

Create a sequential count based on some unique identifiers of the row. When that count changes from 1 to 2 use automation to move it from the sheet to a “trash Sheet” if I had a screen shot of what your working with “using dummy data” I could build the formula for you.

Archie

thanks Mark, see screen shot attached

Screenshot 2024-06-06 100357.png

Archie

https://community.smartsheet.com/discussion/comment/439410#Comment_439410

I'd obviously want to keep the latest version though

Mark.poole

Of Course I use a similar work around with one of the projects I use. Copied rows are always added to the bottom of the sheet. This is a Sequential count for each individual ID and you would just remove once the count goes to 2.

=COUNTIFS(Date:Date, >=Date@row, [ID of Welder/Fitter]:[ID of Welder/Fitter], =[ID of Welder/Fitter]@row))

There is also this formula option. "The one I use that just flags a row with a 1. and removes the 0 with automation)

=IF([Date Created]@row = MAX(COLLECT([Date Created]:[Date Created], [Asset ID]:[Asset ID], =[Asset ID]@row)), 1, 0)

Date is the auto number im using (System created date) , Asset ID is my unique id for the row

Archie

thanks Mark, thats ideal. much appreciated.