Flagging/Removing duplicate rows that do not include comments

Nick Winkley

We have a newsletter that gets sent out, and tracks if anyone opens or clicks through them. That Excel file is uploaded to Smartsheet. We then follow up with anyone who interacted with the newsletter, and follow ups are tracked with comments. Is there a way to delete duplicate rows based on whether or not there are already comments for a company?

Ramzi K

Hi @Nick Winkley,

You will first need to determine what constitutes a duplicate row. If it's just one value in a column then create a new column that counts the occurrence of that value in the entire column. Anything with a count higher than 1 is a duplicate., Then you can move all rows with a value greater than 1 another sheet and delete from there. Or create a filter and manually delete based on the filter whenever you want to clean things up.

As an example, if your sheet has a column called Company and you have field for comments called Comment. Create a field (Checkbox) called Duplicate and put this formula in the each row.

=IF(AND(COUNTIFS(Company:Company, Company@row, Comment:Comment, <>"") > 0, Comment@row = ""), true, false)

This will flag any rows that have a duplicate company name and that have a blank comment (in other words a comment has already been added for the same company on another row.

I hope this help.

Cheers,

Ramzi

Nick Winkley

Hi Ramzi,

Thanks for the reply. I’m specifically checking to see if I can use formulas against the Smartsheet comments column, not a text column called Comments