What is the formula to flag duplicates from a multi-select drop down column?
Here is the formula that I have been working with. Currently I have it build out to where it flags duplicates but only if it is a "single-select". As soon as I select multiple times in the cell, it won't flag the duplicate anymore. My goal is to have any duplicate flag so that people aren't scheduling over a time that has already been booked.
IDEAL SITUATION
- What I really want to do is have a way to limit the drop down of cells based on if they have been already selected. (e.g., If someone selects 9:15AM, then 9:15AM would be removed from all the other drop downs in the column).
- If this is possible, then I wouldn't need to worry about the "Flag for Duplicate" but as of right now, I do not know how to make this possible, which is why I am having to go the route of flagging duplicates (instead of removing options from the dropdown)
Answers
-
Hey @sambittner,
You can make a list of all the available times, set the sheet to public, and create an automation that locks the row once someone has entered their name into the time slot. You could also set another automation to approve or deny the appointment which if denied would clear the slot and unlock the row for another person.
If you want to continue your original direction the formula you are looking for needs to contain the HAS function. The issue with that is you need to check for each time slot option so you would need the list of all times available either inside the formula or listed out somewhere else. I would break the sheet into two with the top portion containing all the times separately on single rows and the second portion for entering appointments. This way the top portion can check to see if any time is listed more than twice.
=IF(AND(COUNTIF([Time of Visit]:[Time of Visit], HAS(@cell, [Time of Visit]@row)) > 2, [Time of Visit]@row <> ""), 1, 0)
-
Hey @Devin Lee ,
Thanks so much for responding.
I entered the formula you gave and this is what I got.
When I select a single time slot (e.g., Row 3 and Row 4), it will flag it as a duplicate (which is good).
However, when I have multiple times selected, (e.g., Row 5, Row 7, and Row 9), it will flag a duplicate but only in the cells that have a single time selected (e.g., Row 6, Row 8, and Row 10). ; not the cells that have multiple times (e.g., Row 5, Row 7, and Row 9).
Technically speaking, the only two rows that shouldn't be flagged as duplicates are Row 1 and Row 2. All of the other rows have duplicates within the column and should be flagged.
Help Article Resources
Categories
Check out the Formula Handbook template!
