IF Multiple Codes have open Status then Duplicate is True

Josh Caldwell

I need some help with a formula that is partially working. I've tried a few ways to code this, but gets an error so I'm probably missing something...

During our intake process using Smartsheet a form can be filled out by multiple people. To my knowledge, there isn't an easy way to prevent multiple submissions for the same entry/customer... such as a pop-up message like, "there is already an open entry in progress".

So, I have a Duplicate column that should have a Checkbox marked TRUE when Customer Code value is Duplicated more than once AND the Status for both is Open (i.e. "Not Started", or "In Progress", or "Watch")

If one entry is Closed (i.e. "Completed", or "Terminated") and the other is still "In Progress" it shouldn't be marked as a Duplicate. In the example below (see screenshot), AGN is duplicate because both are "In Progress". However, ACC shouldn't be checked because only one entry is open ("Watch") and the other is "Completed".

Here's the formula I'm currently using in the Duplicate cell, which is only partially working:

=IF(AND(COUNTIF([Customer Code]:[Customer Code], [Customer Code]@row) > 1, Status@row <> "Completed"), 1, 0)

Josh Caldwell

@Kelly Moore - Thanks for the quick reply! The revised formula almost worked... it marked TRUE only the duplicate values with an open status (that do not have a previous completed/terminated entry), which I may have confused by mentioning closed status. I want to know which ones are duplicate that are currently open...

=IF(AND(COUNTIFS([Customer Code]:[Customer Code], [Customer Code]@row) > 1, COUNTIFS(Status:Status, OR(@cell = "Terminated", @cell = "Completed"), [Customer Code]:[Customer Code], [Customer Code]@row) < 1), 1)

Good news it it was close enough that I just changed @cell to the open values > 1 and that seemed to do the trick!

Here's the formula that works:

=IF(AND(COUNTIFS([Customer Code]:[Customer Code], [Customer Code]@row) > 1, COUNTIFS(Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Watch"), [Customer Code]:[Customer Code], [Customer Code]@row) > 1), 1)

Thanks for your help!

Kelly Moore

@Josh Caldwell

Try this

=IF(AND(COUNTIFS([Escalation ID]:[Escalation ID], [Escalation ID]@row, [Customer Code]:[Customer Code], [Customer Code]@row) > 1, COUNTIFS(Status:Status, OR(@cell = "Terminated", @cell = "Completed"), [Escalation ID]:[Escalation ID], [Escalation ID]@row, [Customer Code]:[Customer Code], [Customer Code]@row) < 1), 1)

To solve the problem of duplicates happening in the first place, one could begin your process with a published sheet that does the lookup first (the published sheet allows access to anyone). I usually refer to this as a form landing page (more technically its the jumping off page). Using the lookup, if a duplicate is found, a notice is displayed. If no duplicate is found then an active url form link is displayed. The active link would push already entered information from the landing page into the form so that the rest of the form could be completed. I use this practice in a couple of my processes where I really need the lookup to happen to inform what fields become visible in the form.

You can find information on active urls here

Kelly

Josh Caldwell

@Kelly Moore - Thank you. I will look into the active urls.

Unfortunately, I tried the formula, but doesn't seem to be detecting the 2 (AGN) "In Progress" as TRUE, which should be checked...

Wondering if the use of Escalation ID in the formula should be excluded, since that is always going to be unique as new entries are submitted... I would just want to know if Customer Code is duplicate and Status is open...

Normally we have it sorted by Escalation ID so dup customer code isn't always obvious. I have it sorted by Customer Code in the screenshot below to show the duplicates more easily, as an example.

Kelly Moore

Hey Josh

Yes, exclude those - I misunderstood and thought these were part of the duplicates.

=IF(AND(COUNTIFS([Customer Code]:[Customer Code], [Customer Code]@row) > 1, COUNTIFS(Status:Status, OR(@cell = "Terminated", @cell = "Completed"), [Customer Code]:[Customer Code], [Customer Code]@row) < 1), 1)

Kelly

Josh Caldwell

@Kelly Moore - Thanks for the quick reply! The revised formula almost worked... it marked TRUE only the duplicate values with an open status (that do not have a previous completed/terminated entry), which I may have confused by mentioning closed status. I want to know which ones are duplicate that are currently open...

=IF(AND(COUNTIFS([Customer Code]:[Customer Code], [Customer Code]@row) > 1, COUNTIFS(Status:Status, OR(@cell = "Terminated", @cell = "Completed"), [Customer Code]:[Customer Code], [Customer Code]@row) < 1), 1)

Good news it it was close enough that I just changed @cell to the open values > 1 and that seemed to do the trick!

Here's the formula that works:

=IF(AND(COUNTIFS([Customer Code]:[Customer Code], [Customer Code]@row) > 1, COUNTIFS(Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "Watch"), [Customer Code]:[Customer Code], [Customer Code]@row) > 1), 1)

Thanks for your help!

Kelly Moore

Awesome! I was conflicted on which way to count - the open or the closed. So glad you worked it out. Great job!

Kelly