Mark Duplicates In Multiselect Dropdown Contacts Column vs Employee Names List?


Ok, we're making a labor schedule, and want to be able to note when and where someone is used multiple times so we can prevent overscheduling someone and ensuring proper manpower. With the above as an example, I want to highlight a cell in [Employees on Job] (multiselect dropdown contacts) column, when one of the values in the cell is used more than once (similar to pic above). Right now, I can see who is used multiple times by checking the [Employee Use Count] that references [Employee Names]@row and count the number of times that value appears in the [Employees on Job] column using the FIND function, but I only know how to reference the line where the total is calculated, not where those values for the count were pulled from. The [Employee Names] column comes from an employee info sheet, and is pulled using the [LINE-ID] and [ROW#] reference with INDEX/DISTINCT/COLLECT trick.

I would like to avoid having to add columns for each employee if possible, because that will change often enough that I don't trust the end user of this sheet to be on top of it.

So, how would I go about comparing [Employees on Job] column against [Employee Names] column and causing conditional formatting for the cell in the [Employees on Job] column? I'm imagining an equation for the checkbox [Duplicate?] column, but am stuck on how to even approach it.

[Employee Use Count]:

=IF([Employee Names]@row <> "", COUNTIFS([Employees on Job]:[Employees on Job], <>"", [Employees on Job]:[Employees on Job], FIND([Employee Names]@row, @cell) > 0, [Employee Names]:[Employee Names], @cell <> ""), "")

Link to above sample sheet: it is open to editing so that anyone can look at the equations. The red in the attached pic is a manual example of what I'm trying to do.

Let me know if I've made anything unclear about my request, because I know I can be wordy. Thank you.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!