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: [email protected]
💡 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 [email protected]
-
@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.....
