Conditional format cells based on overbooking of slot for a resource

Question

Hi,&nbsp;I am trying to highlight resource in column A starting cell A2. Here I will list resources and many will come more than once. This resource shall be shown as booking in time slots starting column B2. Such slots can be many (~300). Booking is shown by marking a x in corresponding cell.If a resource comes more than once, and it&nbsp; has same slot more than once, then this resouce needs to be highlighted.Example data is below:ResourceSlot-1Slot-2Slot-3Slot-4Slot-5Slot-6Slot-7Slot-8Slot-9Slot-10Slot-11Slot-12Slot-13M-1xx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;M-2&nbsp;xx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;M-2&nbsp;&nbsp;&nbsp;xx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;M-3&nbsp;xxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;M-4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;M-3xx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;M-4&nbsp;&nbsp;xxx&nbsp;xx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;In this case, resource M-3 cell will be highlighted.So conditional formatting shall be in Column A.Pls help with suitable formula,&nbsp;

sergeibaklan · Accepted Answer

mkjain&nbsp;
As variant

if I understood the logic correctly. M-4 shares Slot-7, thus also highlighted
Formula is
=SUMPRODUCT(--(MMULT(TRANSPOSE(--($A$2:$A$8=A2)),--($B$2:$N$8="x")) &gt; 1))

Forum Discussion

Conditional format cells based on overbooking of slot for a resource

1 Reply