Monitoring Ongoing Duplicate Entries

Sam McDonald · 09/23/24

Hi I need some advice in being able to track people who have registered for more than one program.

We run multiple programs and need to track if a person registers for more than one for compliance reasons. We have a system where people register and then I use Data Shuttle to upload them into Smartsheet. Currently on the sheet they're loaded into I have a formula column called Duplicate Name Check to track duplicates:

=IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row) > 1, 1, 0)

I then have a column called Approved? which is a manual checkbox for approval.

This information then feeds into a report which is filtered for where Duplicate Name Check is 0 and Approved? is unchecked. To date it's been working okay but it is very manual in that if someone is flagged I need to send an email to the program lead to seek approval and then tick the approved box. Ideally I'd like to see this evolve to a place where I can set up an approval request workflow on the sheet.

The main blocker that I can see at the moment is when a person registers for three program. So they've already registered twice which gets picked up on the report for me to flag and then I manually approve both entries. When they register for a third program, only one entry is on the report. I then need to go into the sheet, unapprove the other two entries so that all three are on my report. This is needed so that when I email to the program lead then have all the program details and can confirm from there if there's an educational need for the person to attend.

Is there a way to do this automatically? Is there an additional part of the Duplicate Name Check formula needed? Is there way to add a formula to the Approved column that it unticks if there are more than two entries? Trying to work out what the trigger would be to send the automation.

It's been haunting my dreams so hoping someone can help! 🙂

Brian_Richardson · 10/03/24

Ok, so it sounds like all you're trying to do is flag when someone has submitted a signup for any program, more than once. Right?

The formula you have all the way at the start of this thread should be working.

=IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row) > 1, true) will check a box anytime someone's name appears more than once. It definitely should not be only flagging a single row…it should flag all rows for that person when they have more than one signup on the tracker.

Setup a checkbox column called "Proceed for Approval".

Then, setup an automation that triggers on row add and uses a condition block to check to see if "Duplicate Name Check" is unchecked. If unchecked, then run an Approval action. That approval action will create a column in your sheet to capture the approval state, which is Submitted, Approved, or Declined. On the "otherwise" part of the condition check (if "Duplicate Name Check" is checked), add an Alert action that will send yourself an alert that there's a duplicate to resolve.

Setup a second automation that triggers when "Proceed for Approval" is checked, and runs an Approval action. You can click the menu and Advanced on that action to point it at the same approval column as the first automation. That will send an approval to the program lead once you've reviewed duplicates.

In your report, just filter for Duplicate Name Check is checked, don't filter for approval. Group the report on the Full Name. That way, you'll see all the submissions and their approval status. In the report include the "Proceed for Approval" checkbox. You can check that box right in the report, which will fire the automation to send the row for approval.

Sam McDonald · 10/04/24

Hi Brian,

Thanks so much for all your help with this issue! With your assistance and inspiration I've finally gotten it to a point where it's 100% automated.

Steps I've taken:

Duplicate Name Check column: =COUNTIF([Full Name]:[Full Name], [Full Name]@row)
Approval Check column: =COUNTIFS([Full Name]:[Full Name], [Full Name]@row, [Approved by PM?]:[Approved by PM?], 1)
Have all Duplicates Been Approved column: =IF([Duplicate Name Check]@row = [Approval Check]@row, 1)
Update request sent to PM for approval when Duplicate Name Check is greater than 1 and Have all Duplicates Been Approved is unchecked

This way it includes all instances where the person has registered so the PM has context.

Thanks again! 😁

Brian_Richardson · 09/24/24

So, to summarize back, it sounds like people are signing up for individual programs and you need the program lead for each program to approve the signup?

If you need to approve each program individually then you might simply want to remove the duplicate name check. Because you need duplicate names to get everything approved, the approval automation will run on a row and send the approval request to the program lead.

You can setup a second sheet with the list of program names and the leads in a contact column, then from the first sheet use =INDEX( {Program Lead}, MATCH ([Program Name]@row, {Program Name}, 0)) as a column formula in the Program Lead column. The {} references are inserted by clicking the Reference Another Sheet link while typing in the formula and then selecting the appropriate column from your lookup sheet.

Then, in automations setup an Approval automation that triggers on Row Add and sends to the person in the Program Lead column. You can adjust the approval message and which columns to show. Then, whenever Data Shuttle adds new rows to your sheet, the sheet will look up the appropriate program lead for that particular signup and send an email to the lead for approval, and will capture their response in a column that the automation will setup for you. All of that with no manual touch needed.

For reporting I'd recommend either simply grouping a report by person and collapsing the groups, so that you don't have to see the details unless you want to. Or, leverage your "duplicate name" checkbox to filter a report to see only 1 entry per person. If you want every row to show all the programs the person has been approved for, you could add a "Program Signups" column with the formula =JOIN(COLLECT([Program Name]:[Program Name], [Full Name]:[Full Name], [Full Name]@row, [Approval]:[Approval], "Approved"), ",") . This formula will collect all the approved program names for the person into one cell that you can show on your report.

If you want to have your duplicate checkbox check itself for all duplicates except the first entry, then you can adjust your checkbox formula like this (this relies on setting up a Row Number column):

Create an Autonumber column called Auto
Create a column called Row Number with the formula =MATCH(Auto@row,Auto:Auto,0)

=IF(COUNTIFS([Full Name]:[Full Name], [Full Name]@row, [Row Number]:[Row Number],<[Row Number]@row) = 1, 1, 0)

Sam McDonald · 10/02/24

Hi Brian, thanks so much for your detailed response.

I only need to flag entries which are duplicate so if a person signs up for two programs I need both to be on a report that I can then send for approval.

The issue I'm having is say that the above scenario was approved via a checkbox or similar but then a person registers for a third program. I'm struggling to figure out how to flag this automatically.

I've got a manual checkbox for the notification above and I've created a new duplicate check:

=IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row) = 1, 1, IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row) = 2, 2, IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row) = 3, 3)))

I've then got another column:

=IF([Duplicate Name Check]@row = 1, 1, IF(AND([Duplicate Name Check]@row = 2, [Approved?]@row = 1), 1, IF(AND([Duplicate Name Check]@row = 3, [Approved?]@row = 1), 1)))

Unsure if I'm on the right track or missing something really obvious.

Brian_Richardson · 10/02/24

So, I'm not quite following your process.

What do you want to happen in each of the following cases? Forgetting the formulas for a second.

Someone signs up for a single program. Do you want automatic approval request? Or is there a manual review step you need to do first?
Same person signs up for a second program. Do you need a step here before approval? Do you need to know the other program they signed up for?
Same person signs up for a third program. Same questions.
Same person signs up a second time, for the third program.
Fourth program signup? Etc

Sam McDonald · 10/03/24

Hi Brian, thanks for taking the time to get back to me!

No action required
We would need to see both programs the person has registered to attend in order to be able to approve or deny. Approval after review.
We would need to see all three programs the person has registered to attend in order to be able to approve or deny. Approval after review.
This would need to be flagged the same as above as we have instances where people sign up multiple times with different email addresses
Same process as above

We have certain criteria for each program so we need to see all particulars to ensure they're essentially not attending more than one program with the same education focus. So while they might attend four programs all with a different focus, program five might have the same focus as program two so we would need to see them all together to make this call.

Brian_Richardson · 10/03/24