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

Andrew G

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: https://app.smartsheet.com/b/publish?EQBCT=427197cf00754a6c8246a4484246780e 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.

Paul Newcome

Try this:

=IF(SUMIFS([Employee Use Count]:[Employee Use Count], [Employee Names]:[Employee Names], HAS([Employees On Job]@row, @cell)) > COUNTM([Employees On Job]@row), 1)

MedaUser

Hi @Andrew G,

Unfortunately, I can't access your example sheet until you open it up to those outside of the organization (I think via Publish). Can you open it up, so I can play around with it?

Thanks,

Travis

Paul Newcome

Try this:

=IF(SUMIFS([Employee Use Count]:[Employee Use Count], [Employee Names]:[Employee Names], HAS([Employees On Job]@row, @cell)) > COUNTM([Employees On Job]@row), 1)

Andrew G

@Paul Newcome That did it! Thank you! I'm pretty new to smartsheet, and I'm not familiar with COUNTM. The equation you wrote is very similar to a lot of things I was trying (sans COUNTM). I'm going to spend some time parsing it to make sure I fully understand it! I poured through the forums trying to find this and everything else was just slightly different and didn't work for my use case.

Paul Newcome

COUNTM simply counts how many selections were made in a multi-select type column.

Basically we SUMIFS the Employee Use Count based on the selections. When compared to the COUNTM it should be equal to. Anything over means that at least one of the people collected has a number higher than 1 in the Employee Use Count column which in turn indicates a duplicate.