Update status of multiple rows at once

Jenn L

My sheet is built to duplicate an original request every 6-months and trigger associated notifications, etc. Each subsequent line item is marked with an “ongoing” status until the end user updates the most current to “closed” status to stop the duplication.

I have logic to “cross-out” and move the line item when status goes to “closed”, but I need this to happen to ALL matching line items based off the unique ID associated to the request. Is there a way for logic to render something like “IF ‘case status’ = closed, FIND and UPDATE matching ‘ID’ to ‘case status’ = closed”.

(Fair warning, I'm only intermediate Excel and SmartSheet novice.)

adp_cl

Needing to identify the newest row among any duplicates is a common requirement, and there have been some great suggestions in the community on how to achieve this. In most cases, it involves a checkbox column with a column formula that flags the older duplicates. In your case, you could then use conditional formatting to cross them out or move them to another sheet.

Examples: Is there a way to catch duplicates? — Smartsheet Community, Duplicate Row Column Formula? — Smartsheet Community, Work around for duplicates to only show the most updated row — Smartsheet Community, Duplicate Row Column Formula? — Smartsheet Community

Jenn L

Thank you, I was able to use these formulas to identify and flag my duplicate items using this formula

=IF(AND([TRAILS CASE #]@row <> "", COUNTIFS([TRAILS CASE #]:[TRAILS CASE #], [TRAILS CASE #]@row) > 1), "MATCH")

My next step is to update the status of ALL the items based on the newest one changing to CLOSED, and not before. I know I could use the duplicate flag + another trigger to update the status, but it shouldn't be occurring until the last item updates to CLOSED by a user.

My desired end result: all items matching TRAILS Case # with last item "CLOSED" get moved off the sheet.