Finding Duplicates

Hello,

I have several sheets. One every month with at least 2000 rows per month. I import the information from an outside source via Excel. Long story short, there is a high probably ability of getting duplicate information from the outside source. Once imported from Excel into Smartsheet is there a way to fnd & delete duplicate reocrds from the table/sheet?

Thank you,

TLd

Find more posts tagged with

Workspaces

Sheets

Reports

Support

Solution Center

Project Management

Importing and Exporting

Comments

Joel Johnson

Hello Tammy,

As far as I know, there isn't a built in function for finding duplicates. Howerver, I have located duplications in a couple of ways in the past and then just deleted them manually.

Here is a quick example of one method:

After I have imported the data, I create a column called "Is Duplicate" And copy a formula into each cell that looks something like this:

=COUNTIFS($[Project Name]$1:$[Project Name]$20, [Project Name]1)

In this example it basically counts how many times that "project name" is in the "project name" column. If it is more than 1 time, I know I have a duplication. I then used conditional formating to highlight the problematic areas. I can now choose which ones I should delete. Here is an simple image of what it could look like.

Hope it helps.

I do have another method I have used, if you are not worried about how the information is sorted. I would sort by the "Project Name" and then the calculation in the "Is Duplicate" column just looks at the row above it and checks if it is the same. It basically highlights the duplications, but not the first incident. Then I would filter the column to just view the duplications, and delete the rows, without a chance of deleting the first incident. I can send you that formula or example of that if you need.

Good luck.

bnunna

Thanks Joel. It helped. I am new to Smarsheet. Figuring out a way to find duplicates in Smartsheet similar to Excel and bumped in to this thread.

Joseph Edwards

Hello everyone,

I seem to be having trouble here. I have tried a few ways but ultimately, I am stumped. I am very new to SmartSheet, and I am still familiarizing myself with spreadsheets overall.

The Situation:

I am trying to detect duplicate addresses in a column named Full Address, and they do include the entire thing, street, city, zip, state, and country all within the same cell on each row.

I feel this may or may not provide a wrench, but I can't figure out the right way to write this formula.

Overall Goal:

I would like to keep the duplicates, and I would like to count them as well as use conditional formatting to color them in.

Thank you all for your time!

Andrée Starå

Hi Joseph,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Brittany.Smartsheet

For reference, there is also a way to find the number of duplicates in a column, by using the DUPLICATE function along with a COUNTIF.

For example, if the column I am wanting to count for duplicates is named "Buyer" and I want to find distinct Buyer names that are entered in that column, I would use the following formula:

fx=COUNTIF(DISTINCT(Buyer:Buyer))

This might help you with at least knowing if you have duplicates in your row, and you could apply a conditional formatting rule to further differentiate the duplicates.