Search within all multi-selected fields for duplicates

Rylan Funk

Hi,

I am wondering if there is a way to detect when there are duplicates of a multi-selected item in another row.

My formula so far: =IF(OR(ISBLANK(Checkin@row), ISBLANK(Checkout@row)), "", IF(COUNTIFS(Checkout:Checkout, >=(Checkin@row + 1), Checkin:Checkin, <=(Checkout@row - 1), SiteNumber:SiteNumber, SiteNumber@row) - 1 > 0, "Double Booked"))

I am basically working towards a booking system. I want the Formula field to populate "Double Booked" when two booking's dates overlap, AND share a "SiteNumber". I have figured out the overlapping dates part, and the SiteNumber part works when the filed type is text/number, but that limits me to only allow one site number in the SiteNumber field.

Thanks!

Leibel S

@Rylan Funk

See below a discussion where I worked a similar need (substitute the TASK ID field for your date fields instead).

Let me know if this works for you, feel free to comment if you need more help with it.

https://community.smartsheet.com/discussion/74520/highlight-duplicate-values-with-a-criteria-within-multiple-select-column