Trying to find duplicates in a column so I can alert the submitter and remove the row.

After much research, I am still stumped. I have set up a Smartsheet that will allow users, via a form, request a date and time slot for hoteling (work desk reservation). I have a link in the form to the dashboard so they can look at the calendar to see if there is availability. I am sure some will not look at the calendar and submit the request. I find the duplicates with a helper column combining the date, time slot, and desk number under the column name Dup Compare

=[Start Date]@row + "," + [Time slots]@row + "," + [Hotel Spaces]@row

Then find the duplicates with the following formula.

=COUNTIF([Dup Compare]:[Dup Compare], [Dup Compare]@row)

So far so good. Now here is where I am stumped. I've found that there is a duplicate if the formula above returns something greater than 1, but not sure where the matching duplicates are in the sheet. I have the system "Created" column. What I want to do is find the matching duplicate(s), compare the "Created" dates and keep only the oldest date (first come first serve). I know how to do alerts and deleting rows. It is just finding the corresponding duplicates so I can kick off automation.

For now, I am going to alert someone if there is a duplicate, conditional format highlight the rows, and have the alerted person manually remove the row(s) and notify the submitter. I would love not to not have this manual process so if anyone has an idea, please share! Thank you!

Find more posts tagged with

duplicates

Accepted answers

Aaron Saunders

From what you have, add in a system generated column # Auto Number and call it "Row ID" or something similar. then for your duplication check formula put in this formula (adjusting for your specific column names) Summary Column is the column where you have this formula =[Start Date]@row + "," + [Time slots]@row + "," + [Hotel Spaces]@row

=IF(COUNTIFS([Summary Column]:[Summary Column], [Summary Column]@row, [Row ID]:[Row ID], "<" + [Row ID]@row) > 0, "Duplicate", "Unique")

Your results should look like this:

Then from there you can create an automation to kick an update request to the person who created the booking explaining the station they requested for the {{date}} and {{time}} is already booked, then just have them change the booking in that update and you should be good to go!!

All comments

Kerry St. Thomas

I think I'm following. If I'm correct, you basically want to mark anything that's NOT the first item in your Dup Compare column. With that being the case, here's the solution I'd suggest:

If you don't have an Autonumber column, add one. Make sure the output is STRICTLY number - if you already have an autonumber column with formatting in it, create a helper column to just strip the numerical info and return that (use LEFT, RIGHT, MID, and/or VALUE).Create a column type Checkbox. In that column, add this as a Column Formula:

=IF(MIN(COLLECT(Autonum:Autonum, [Dup Compare]:[Dup Compare], [Dup Compare]@row)) = Autonum@row, 0, 1)

This formula will check the box only when there is more than one row of your Dup Comparem and will mark only the 2nd and higher instance.

You'll now be able to hang your automations off those marked rows. Good luck!

Aaron Saunders

=IF(COUNTIFS([Summary Column]:[Summary Column], [Summary Column]@row, [Row ID]:[Row ID], "<" + [Row ID]@row) > 0, "Duplicate", "Unique")

Your results should look like this:

Kris Torre

Aaron Saunders, that worked like a charm. Thank you so much! I am going to have to study that formula to see WHY it works. I think this is also assuming that new rows are being added to the bottom rather than the top, correct?

Kerry St. Thomas, thanks for your input as well. I tried Aaron's first so didn't need to try yours.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.