How to delete rows with duplicate data in Smartsheet (Community)

table of contents

first

If you have duplicate data in column 1 in Smartsheet, there are several ways to remove or disable the duplicate rows. In this Q&A, we will show you how to move duplicate rows to another sheet using column move automation.

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

Question summary

Eden asked how to remove or disable duplicate rows when there are duplicate data in column 1 in Smartsheet.

Answer summary

jmyzk_cloudsmart_jp isHow to move duplicate rows to another sheet using column move automationI proposed. This method uses three helper columns to identify rows:

Row Number: Assign a unique number to each row.
RANKEQ: Rank rows with the same column 1 value.
Duplicate: Marks rows with RANKEQ values greater than 1 as duplicate rows.

Use the Move Column workflow to move duplicate rows to another sheet.

Summary

An effective way to remove rows with duplicate data in Smartsheet is to use automated column movement. This method requires creating helper columns such as row number, Rankeq, duplicates, etc. You can also use the Move Columns workflow to move duplicate rows to another sheet.

Other tips

Mr. Eden isHow to set Row ID and automatically calculate Row NumberI asked the question again.
In response, Andre, a popular Community user, explains how to do this using the classic MATCH function.

Mr. Eden isHow to set Row ID and automatically calculate Row NumberI asked the question again.

In response, Andre, a popular Community user, explains how to do this using the classic MATCH function.

Description

Row ID (Row ID) does not necessarily match the row number (Row No or Row Number) due to row deletions, additions, etc.
If you want to process based on the row number (Row No or Row Number) like the RANKEQ function, you can get the row number (Row No or Row Number) by using the MATCH function.　
In the example,MATCH([Row ID]@row, [Row ID]:[Row ID], 0) and Row ID, but if there is a column consisting of unique values that will never be the same, you can also use that data.
For example, if you know that multiple rows will not be added at the same time, you can use Created By instead of Row ID.

=MATCH(Created@row, Created:Created, 0)

When copying or moving a row, if the row number (Row ID) column cannot be used, such as when an error occurs if the copy or move destination has a row number (Row ID) column with a different name, use the creation date and time. You may use the technique above.

Error when copying or moving rows

The column names of the source and destination columns must be the same. If the source and destination sheets have different column names or column types, the data in the copied column appears in a new column to the right of the existing column on the destination sheet. If so, scroll to the right, find the data, copy it, and paste it into the desired column.

If the source sheet and destination sheet contain columns with the same name, and only one sheet uses a system column for that column, the message "This row could not be moved/copied. Move/Copy Please make sure the source sheet and the destination sheet contain the same system columns...'' error.

Please refer to the help article below for details.

https://help.smartsheet.com/ja/articles/770589-copying-a-row-to-another-sheet#toc—2

https://help.smartsheet.com/ja/articles/504748-moving-rows-columns-cells

Translation of correspondence

Eden ✭✭

11/24/23

If there is duplicate data in column 1, how can I delete or disable the row with duplicate data? thank you!

best answer

jmyzk_cloudsmart_jp ✭✭✭✭✭✭

11/24/23

Hello@Eden
I would use Automate row movementMoves (or deletes from the sheet) the row that has the same column1 value as the previous row (the row is higher in the sheet).
To do this, we need helper columns such as Row Number, Rankeq, Duplicates, etc.
Lankeku

=RANKEQ([row number]@row, COLLECT([row number]:[row number], [column 1]:[column 1], [column 1]@row), 1)

Meaning: Collect and rank row numbers with the same Column1 value.

repeat

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

Automating row movement workflows
Once the values in the "Duplicate" column are checked, move that row to another sheet.

smart seat

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