Removing Duplicates

Marcus Brooks

I've added a helper column that flags them as a check box but how do i add another to eliminate the duplicates? How do I add another to remove duplicates automatically?

Ramzi K

Are you trying to delete the duplicates or just show a list if unique values?

Marcus Brooks

Delete the duplicates showing unique values. So for example, the list of locations would only show 1 of each location and does not show any repeating values. So as pictured above, I would like to remove the extra F2E's, F1E's, etc but keep at least one of them

Marcus Brooks

@Ramzi

MCorbin

I use Automation to move those rows to an "Archive" sheet, so they get out of my main sheet. Then I periodically go in and delete everything in the archive.

The "Archive" sheet is just a copy of the main sheet.

Marcus Brooks

@MCorbin does automation keep at least 1 of the duplicates and remove the extras?

MCorbin

You would either need to manually remove 1 of your check marks, or build a counter into your formula.

This formula looked in a sheet for any other rows matching the first one (in this case, looking at worksheet name). The first one it finds, it assigns a 1, all the rest get a Zero. Then you would update your automation to point to this column and only move rows where your checkbox is checked and this value is 0

=if(countif([worksheet name]$1:[worksheet name]@row,[worksheet name]@row)<>1,0,1)

***The problem with this formula - if it's a large sheet (>5k rows), it does slow down opening the sheet.***

Marcus Brooks

@MCorbin Thanks - that formula is producing the same result.

I was FINALLY able to find something that works. I used the formula here (https://community.smartsheet.com/discussion/67208/how-to-get-distinct-values-from-a-column-then-shows-their-maximum-number) to filter out unique values. I then used your suggestion for non-blanks in the report.

Nasir@EBC

Dear Marcus,

I have developed a workaround that will allow you to pull only the unique rows of any duplicate records.

Please let me know if you are still looking for some help on this!

Nasir

Shahara Anderson

@Nasir@EBC

Dear Nasir,

I am interested in your workaround. I managed to connect my SQL server SMARTSHEET via excel. The only issue is that when I refresh the excel sheet and trigger the upload to SMARTSHEET, it re-uploads the entire data set. I need a way to filter out the first instance of data.

Thanks,

Shahara

smartsheetdev.io

@Shahara Anderson

Dear Shahara,

Below is a link with a video to show how to highlight a duplicated values via API.

https://youtu.be/EXI6iFN6Q2I

In regards to connecting your database with Smartsheet it's possible, please send me an email to discuss further info@smartsheetdev.io

MBonanno

@MCorbin Can you replicate this formula without the Absolute reference so that I can convert to a column formula? I have not been able to do so successfully...

=if(countif([worksheet name]$1:[worksheet name]@row,[worksheet name]@row)<>1,0,1)

MCorbin

Unfortunately, no, I haven't been able to do it successfully either. I'd love to be able to do it.....

Dantastic

This should be a built in feature…

Marcela

Hi! @Dantastic

This actually sounds like a great idea! You can submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea! The Product team reviews top-voted post once per month and provides a status update.

Cheers!

Stefano Ferretti

a very "simple" way of doing it is to create 2 separate DataShuttle work:

first one you can offload your data as a .csv attachement (even to the same sheet).

Second one you create an upload work, from that attachment to a different sheet, and select the column where you have your duplicate as the unique identifier. In your case above that would be the 'Location' Column.

Your new sheet will only have 1 row x location, hence excluding all your duplicates.