Automatically remove a row with the same "ID" as another row
![Archie](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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…
- 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?
- 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…?
Best Answer
-
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
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
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.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
thanks Mark, see screen shot attached
-
I'd obviously want to keep the latest version though
-
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
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
thanks Mark, thats ideal. much appreciated.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!