Remove Duplicate Rows Leaving One Row

Carlee Schiffner
Carlee Schiffner ✭✭✭
edited 06/06/22 in Formulas and Functions

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!

Answers

  • Carlee Schiffner
    Carlee Schiffner ✭✭✭
    edited 06/06/22

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

  • Jeff Reisman
    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thanks Jeff! I will try this out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!