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.

Hello<\/p>

I have this system where workers report with a form their done tasks and locations for those specific tasks. I am trying to avoid the option for them to do duplicate inputs.<\/p>

It works like this: worker chooses a task in form cell, then the locations where he did that task. They do multiple locations per form input, up to 20 or so, that is why the location is a multiple drop down list. Total list of locations is ca 1000. If one of those locations is reported for that specific task, then it is 100% completed and SHOULD NOT be reported again, however that happens, due to human error and so.<\/p>

That is why I trying to come up with a helper column with some formula where it is highlighted that work for this location and this specific task there has already been reported.<\/p>

Example of the form inputs look like this: new entry is added as the top row, leftmost column is is the Locations (a, b, c etc.) with multi select column, middle column is the Task ID, and rightmost column is where duplicates are highlighted.<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/2S6QGQYVKUQS\/0capture6766-jpg.jpg<\/a><\/p>

My goal is so that in the right column the Red flag appears automatically to highlight that a location of row number 3 has already been reported in row number 5 for that specific task, same with row number 6 and 8. In this example I have marked the flag manually.<\/p>

I figured I could make something with COUNTIFS to first count all locations with specific criteria of the task and compare it within the task ID, but I could not get it to work.<\/p>

Link to the sheet, feel free to modify it: https:\/\/app.smartsheet.com\/b\/publish?EQBCT=47d9ee0c958d43b4884212959f1cba25<\/a><\/p>

I would really appreciate some anybody has some ideas on how to proceed.<\/p>

Thank you<\/p>","bodyRaw":"[{\"insert\":\"Hello\\nI have this system where workers report with a form their done tasks and locations for those specific tasks. I am trying to avoid the option for them to do duplicate inputs.\\nIt works like this: worker chooses a task in form cell, then the locations where he did that task. They do multiple locations per form input, up to 20 or so, that is why the location is a multiple drop down list. Total list of locations is ca 1000. If one of those locations is reported for that specific task, then it is 100% completed and SHOULD NOT be reported again, however that happens, due to human error and so.\\nThat is why I trying to come up with a helper column with some formula where it is highlighted that work for this location and this specific task there has already been reported.\\nExample of the form inputs look like this: new entry is added as the top row, leftmost column is is the Locations (a, b, c etc.) with multi select column, middle column is the Task ID, and rightmost column is where duplicates are highlighted.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/2S6QGQYVKUQS\\\/0capture6766-jpg.jpg\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/2S6QGQYVKUQS\\\/0capture6766-jpg.jpg\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\\n\\n\\n\\n\\n\\nMy goal is so that in the right column the Red flag appears automatically to highlight that a location of row number 3 has already been reported in row number 5 for that specific task, same with row number 6 and 8. In this example I have marked the flag manually.\\nI figured I could make something with COUNTIFS to first count all locations with specific criteria of the task and compare it within the task ID, but I could not get it to work.\\nLink to the sheet, feel free to modify it: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=47d9ee0c958d43b4884212959f1cba25\"},\"insert\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=47d9ee0c958d43b4884212959f1cba25\"},{\"insert\":\"\\nI would really appreciate some anybody has some ideas on how to proceed.\\nThank you\\n\"}]","format":"rich","dateInserted":"2021-01-06T09:36:23+00:00","insertUser":{"userID":122404,"name":"Sander K","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Sander%20K","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-11-02T13:44:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/74520\/highlight-duplicate-values-with-a-criteria-within-multiple-select-column","embedType":"quote","name":"Highlight duplicate values with a criteria within multiple select column"}"> https://community.smartsheet.com/discussion/74520/highlight-duplicate-values-with-a-criteria-within-multiple-select-column