Remove Duplicate Rows Leaving One Row

Carlee Schiffner

Hello! I am looking for a way to remove duplicate rows from a sheet based on the project name but I need to leave one row behind.

I created a helper column to count duplicates with this formula: =COUNTIFS([Project Name]:[Project Name], [Project Name]@row). Then planned on moving the rows with a count greater than 1 to an archive sheet with an automation.

But I realized that most of my rows would move to the archive sheet and one row with a duplicate value would not be left on my main sheet. Any suggestions?

Thanks!

Carlee Schiffner

Here is an attachment of my helper duplicate column and the project name.

Jeff Reisman

@Carlee Schiffner

This might fail due to circular logic restrictions, but you can try.

You could try using a helper sheet with a list of your project names, and do you duplicate counting there. One row for each project name.

On your main sheet, convert your duplicate check field to instead indicate with a "Y" if the there's a row in the helper sheet with that project name that has a value over 1. Set an automation to run on a daily or hourly basis that sets the value of another helper column ("Archive Row?") on your main sheet where the Duplicate Check field equals Y.

So your main sheet would have the project name, the duplicate column that's either Y or blank, and then the "Archive Row?" column either Y or blank.

Your last automation would run an hour after the first one. This one would move all rows to archive where 'Archive Row?' is equal to Y.

Carlee Schiffner

Thanks Jeff! I will try this out.