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?
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.
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!
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.
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...
Unfortunately, no, I haven't been able to do it successfully either. I'd love to be able to do it.....
I'm working on a compensation request form that follows these steps: An HR Business Partner completes the Compensation Request Smartsheet Form that then triggers an alert to our Compensation Manager. Our Compensation Manager will insert the requested information. The HR Business Partner will be alerted when the…
I have many many sheets where column names are the same but data types are different (ex: some "Assigned to" columns are text/numbers and some are contact list, some "Project Name" columns are text/numbers, and some are dropdown list.) The choices in value type are driven by the many teams specific needs. This creates…
Is it possible to format a singular cell in a sheet to total the above numbers add in each cell of the column.
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.