Duplicate folumla

Adarian Searcy

Hi,

I am creating a formula to identify duplicate 'Date of Visits' by 'Clinician Employee ID.' While I have successfully created this formula using a single column lookup value, I am encountering difficulties when trying to apply it to multiple lookup columns.

Example below: I am looking for a 2 in the Duplicate Schedule column for the "Date of Visit" 04/24/25.

Formulas attempted:

=COUNTIFS([Date of Visit]:[Date of Visit], [Date of Visit]@row, [Clinician Employee ID]:[Clinician Employee ID], [Clinician Employee ID]@row)

=IF(COUNTIFS([Date of Visit]:[Date of Visit], [Date of Visit]@row, [Clinician Employee ID]:[Clinician Employee ID], [Clinician Employee ID]@row) > 1,1)

Any assistance is greatly appreciated.

prime_nathaniel

@Adarian Searcy

The cheater way "kind of", concatenate your employee id + date value into one long text string. Search that as 1 variable.

Adarian Searcy

@Andrée Starå could you assist me with working out theis duplicate formula list above. Not sure if the ask of the Smartsheet system is too much, perhaps one column is blocking the duplicate comparison of the other column?

I've also tried new formulas listed below:

=IF(COUNTIFS([Clinician Employee ID]:[Clinician Employee ID], [Clinician Employee ID]@row, [Date of Visit]:[Date of Visit], [Date of Visit]@row) > 1, 1, 0)

=IF(COUNTIFS([Clinician Employee ID]:[Clinician Employee ID], [Clinician Employee ID]@row, [Date of Visit]:[Date of Visit], [Date of Visit]@row) > 1, "Duplicate", "Unique")

Kelly Moore

Hey @Adarian Searcy

The syntax of your formulas are correct. The first formula should have provided a 2 in both your 4/24/25 rows, the 2nd formula should have provided a 1 in both rows. How is your date being populated? As a test, if you manually type in the date on the two 4/24/25 rows, does your formula work?

Kelly

Paul Newcome

Are there any employee ids that do not have leading zeros?

Adarian Searcy

@Kelly Moore,

Both the EEID and dates are populated from a form. The EEID is selected from a dropdown menu, and the dates are chosen from a calendar. Despite manually entering the dates, the Duplicate column still shows "0" in all cells.

@Paul Newcome,

Unfortunately, there are none available at the moment. I did create two ghost EEID starting with the numbers 1 and 2, the formula appears to have worked in that case.

Formula used:

=IF(COUNTIFS([Clinician Employee ID]:[Clinician Employee ID], [Clinician Employee ID]@row, [Date of Visit]:[Date of Visit], [Date of Visit]@row) > 1, 1, 0)

Paul Newcome

Ok. Try this… Insert a text/number column called "ID Helper" and put this column formula in it:

=[Clinician Employee ID]@row + ""

(that's a "plus quote quote" there on the end)

Reference this new helper column instead of the regular id column in your formula and see if that works.

Adarian Searcy

@Paul Newcome @prime_nathaniel @Kelly Moore, hi, I changed the formula from "Clinical Employee ID" and "Date of Visit" to the lookup values of "Clinician Name" and "Date of Visit" and the formula is working. I believe the issue was related to the system apostrophe (')00000000 verse a 1000000.

Thanks everyone for your time!😁

Andrée Starå

https://community.smartsheet.com/discussion/comment/479963#Comment_479963

Hi,

I hope you're well and safe!

I missed this, but I'm glad to see that you got it working.

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!