Hello
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.
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.
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.
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.
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.
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.
Link to the sheet, feel free to modify it: https://app.smartsheet.com/b/publish?EQBCT=47d9ee0c958d43b4884212959f1cba25
I would really appreciate some anybody has some ideas on how to proceed.
Thank you
