Delete rows with duplicate data

Eden · 11/24/23

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 · 11/24/23

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 · 11/24/23

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 · 11/26/23

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

Thank you very much!!!😀

Eden · 12/29/23

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å · 12/29/23

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 · 08/24/24

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 · 08/29/24

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

Delete rows with duplicate data

Best Answer

Answers

Help Article Resources

Categories