Removing Duplicates
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?
Answers
-
Are you trying to delete the duplicates or just show a list if unique values?
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
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
-
-
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.
-
@MCorbin does automation keep at least 1 of the duplicates and remove the extras?
-
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.***
-
@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.
-
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
-
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
-
Dear Shahara,
Below is a link with a video to show how to highlight a duplicated values via API.
In regards to connecting your database with Smartsheet it's possible, please send me an email to discuss further info@smartsheetdev.io
-
@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)
-
Unfortunately, no, I haven't been able to do it successfully either. I'd love to be able to do it.....
-
This should be a built in feature…
-
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!
