Delete rows with duplicate data

Eden

Can you please tell me how to delete or nullify the row where the duplicate data is located if there is a duplicate data in column 1? Thank you!

jmyzk_cloudsmart_jp

Hi @Eden

I would use the move rows automation to move (or delete from the sheet) the row with the same column1 value as the previous row (the row is in the upper position in a sheet).

We need helper columns like row no, Rankeq, and Duplicates to do that.

Rankeq

• =RANKEQ([Row No]@row, COLLECT([Row No]:[Row No], [Column1]:[Column1], [Column1]@row), 1)
• Meaning: Collect Row Nos that have the same Colum1 values and rank them.

Duplicates

• =IF(Rankeq@row > 1, 1, 0)

Move rows workflow automation

When the "Duplicates" column values become checked, move the row to another sheet.

https://app.smartsheet.com/b/publish?EQBCT=e80c8493e3714f75b3ae64739d2bd5a9&da_disableDeepLink=true

jmyzk_cloudsmart_jp

Hi @Eden

I would use the move rows automation to move (or delete from the sheet) the row with the same column1 value as the previous row (the row is in the upper position in a sheet).

We need helper columns like row no, Rankeq, and Duplicates to do that.

Rankeq

• =RANKEQ([Row No]@row, COLLECT([Row No]:[Row No], [Column1]:[Column1], [Column1]@row), 1)
• Meaning: Collect Row Nos that have the same Colum1 values and rank them.

Duplicates

• =IF(Rankeq@row > 1, 1, 0)

Move rows workflow automation

When the "Duplicates" column values become checked, move the row to another sheet.

https://app.smartsheet.com/b/publish?EQBCT=e80c8493e3714f75b3ae64739d2bd5a9&da_disableDeepLink=true

Eden

https://community.smartsheet.com/discussion/comment/406463#Comment_406463

Thank you very much!!!😀

Eden

Dear jmyzk,

Thank you for your answer. Could you please tell me how to set the Row ID column to calculate the Row ID automatically? Thank you!

Andrée Starå

Hi @Eden

I hope you're well and safe!

Here's the formula.

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sebastian Tabilo

Hi! @Andrée Starå

Can you help me, please?

I followed all the steps, but when running the workflow all the rows were moved to the other sheet not just "checked" ones.

Thank you!

cjbill8872

Also tried this, it's not working. The "Duplicate" column never changes, even if there are duplicates.