Grouping/categorising data for conditional formatting

[Tobes]

Hi everyone,

I create a list of payment signatories each week and as a control I need to create a conditional format to flag when two people from the same department have been put on the same day. Instead of creating several different conditional formats to say 'flag when 'x' is put with 'x'' and so forth, is there a better way of saying 'x' is in finance, 'x' is in marketing etc so when two 'finance' people are put together, the conditional format kicks in.

Any help greatly appreciated cheers!

 

[DRSteele]

Highlight the a column you wish to format (like A2:A8 of column Date in my example). Invoke the Conditional Formatting process and select Use a Formula..., then paste this exactly in the Format values where... box =SUMPRODUCT(--(A2=$A$2:$A$8),--(C2=$C$2:$C$8))>1 and select a desired format (like an orange fill) and Apply.

<strike></strike>

	A	B	C
1	Date	Workman	Department
2	9/24/2015	Dave	Finance
3	9/24/2015	Alice	Marketing
4	9/25/2015	Dave	Finance
5	9/27/2015	Bob	Sales
6	9/27/2015	Fred	Sales
7	9/28/2015	Alice	Marketing
8	9/29/2015	Dan	Marketing

<tbody>
</tbody>

Sheet6​