Remove duplicates based on Modified Date

Hi team, any suggestion is appreciated!
I have 4 sheets:

Sheets A-C: Individual project trackers
Sheet D: Main project sheet

What I need: Setting up a workflow that copies row updates in Sheets A-C over to the Main Sheet (Sheet D). This will ensure the Main Sheet includes all project statuses so I can report on them.

Current Status: In the Main Sheet (sheet D), I also want to set up a workflow where if a new row comes in with an existing ProjectID, the row with the older Modified Date gets moved to an Archive Sheet. This keeps only the latest status in the Main Sheet.

Problem: When a project is updated in the Individual Tracker sheets (A-C) and the row gets copied to the Main Sheet (D): A new row is created with the same ProjectID. Modified Date for all rows with that ProjectID is updated to the latest. Therefore, I can’t move older records out based on the Modified Date.

Does anyone know how to solve this problem or have suggestions for a better implementation?

Find more posts tagged with

Formulas

automated archive

Accepted answers

All comments

SoS | Dan Palenchar

Hello @zdy520kobe,

When you say "report on" what exactly do you mean? You might not need to get all of the rows into one single Sheet. As far as getting a roll up to see the status of all of your projects at once you have some options:

Option 1: Control Center Summary Sheet

If you have Control Center you can easily do this by making a Portfolio Summary Sheet with Status as a profile data field.

Option 2: Report on your Project Sheets

Whether or not you have Control Center you could also generate a Report and see all of your project Status at a glance.

If the projects are in one Workspace set the Report scope to that workspace (see the video below on how to do this).

If the projects are in separate workspaces you can:

Use Dynamic Report w/ Control Center (though redundant w/ Blueprint Summary), OR
If you don't have Control Center you will have to update the report manually to include/exclude projects as they start/finish (so in this case, easier to have them in one workspace).

https://www.youtube.com/watch?v=kJhcCt0XcXM

Option 3: Identify most recent project row in database and push to report

If you want to maintain your current architecture an easier solution than removing the duplicates would probably be to use a formula to identify the most recent project row. I'm going to assume their is a column in your Sheet called Project ID that is unique for the project in question. You can use a formula to check if the Created value in a particular row is equal to the most recent Created values for all instances of the Project ID in that row. The formula below will do this and is meant to go into a checkbox column, it will check the boxes for all of the Project IDs that were added most recently.

=IF([Created]@row = MAX(COLLECT([Project ID]:[Project ID], Created:Created, Created@row)), 1)

You can then make a report and filter it based on that checkbox being checkd.

Option 4: Implement Automatic Duplicate Removal Solution

Removing duplicates automatically is actually pretty complex, I did figure out a solution for this that you can see in the video below. This will show you how to use a series of formulas and automations to dynamically identify and remove duplicate rows… it's a set it and forget it solution but might take some setup and adjustment to your Sheet structure.

https://www.youtube.com/watch?v=i3DOqQXJXvk

Hope this helps!

zdy520kobe

Wow, thank you so much Dan! I will try these solutions out!

zdy520kobe

Hi Dan @dan palenchar, I tried Option3 as it aligns with my current flow. But the formula keeps throwing an error as " Invalid Operation". I tried change the "=" to "<" to find the older time as well, but it did not work. Would you please advise?

zdy520kobe

Hi Dan @Dan Palenchar, thank you for your suggestions! I tried option 3 as it aligns with my current flow. But when I input the formula, it keeps throwing an error as " Invalid Operation". I tried to change the "=" to "<" to flag out the older entries, but it did not work. Would you please advise?

SoS | Dan Palenchar

Whoops I made a mistake, try

=IF([Created]@row = MAX(COLLECT(Created:Created, [Project ID]:[Project ID], [Project ID]@row)), 1)

@zdy520kobe

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.