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

Options

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…?

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/06/24 Answer ✓
    Options

    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    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.

  • Archie
    Archie ✭✭✭
    Options
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/06/24 Answer ✓
    Options

    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.

  • Archie
    Archie ✭✭✭
    Options

    thanks Mark, thats ideal. much appreciated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!