Highlight all jobs belonging to one client

[mattr93]

I have a spreadsheet with a list of jobs and the client.
Any job that has an order has an expiry date of 10 years, anything else has an expiry date of 1 year.
Any job that is still in date has been highlighted Green using conditional formatting.

I would like to highlight all rows with the same client name if that client has at least 1 entry which is an order (so that the order overrides the expiry date). I have been racking my brains all day over this so any help would be much appreciated. I'm only a basic user of excel so I do apologise if i have missed something rather simple.

 

[thisoldman]

Oops! What I posted doesn't work.

 

[thisoldman]

Try this.

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Company[/TD]
[TD]Order date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alpha[/TD]
[TD="align: right"]02/01/2006[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Beta[/TD]
[TD="align: right"]07/01/2010[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Gamma[/TD]
[TD="align: right"]02/01/2009[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Alpha[/TD]
[TD="align: right"]02/01/2007[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Beta[/TD]
[TD="align: right"]02/01/2007[/TD]
[/TR]
</tbody>[/TABLE]

Conditional formatting formula applied to A2:B6.

=COUNT(1/(($A2=$A$2:$A$6)*($B$2:$B$6>=DATE(YEAR(TODAY())-10,MONTH(TODAY()),DAY(TODAY())))))

 

[mattr93]

My spreadsheet currently looks something like:

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Organisation[/TD]
[TD]Subject[/TD]
[TD]Status[/TD]
[TD]State[/TD]
[TD]Invoiced[/TD]
[TD]End Date[/TD]
[TD]Expiry Date[/TD]
[TD]In Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Client A[/TD]
[TD]Job A[/TD]
[TD]Complete[/TD]
[TD]Quotation[/TD]
[TD]No[/TD]
[TD]17/09/2015[/TD]
[TD]17/09/2016[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Client A[/TD]
[TD]Job B[/TD]
[TD]Complete[/TD]
[TD]Order[/TD]
[TD]Yes[/TD]
[TD]24/09/2015[/TD]
[TD]24/09/2025[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Client A[/TD]
[TD]Job C[/TD]
[TD]Complete[/TD]
[TD]Order[/TD]
[TD]Yes[/TD]
[TD]12/10/2015[/TD]
[TD]12/10/2025[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Client A[/TD]
[TD]Job D[/TD]
[TD]Complete[/TD]
[TD]Quotation[/TD]
[TD]No[/TD]
[TD]25/10/2015[/TD]
[TD]25/10/2016[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Client A[/TD]
[TD]Job E[/TD]
[TD]Complete[/TD]
[TD]Quotation[/TD]
[TD]No[/TD]
[TD]04/11/2015[/TD]
[TD]04/11/2016[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Client A[/TD]
[TD]Job F[/TD]
[TD]Complete[/TD]
[TD]Order[/TD]
[TD]Yes[/TD]
[TD]16/11/2015[/TD]
[TD]16/11/2025[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

I have used conditional formatting to highlight the row dependant on the value of Column H.
If H = True then the row is Green. If H = FALSE then the row is RED.

I need the TRUE values to override the FALSE values if the value in Column A is the same.

Hopefully that explains what i need a bit better

 

[thisoldman]

Rules apply to $A$2:$H$7

Order of the rules, top to bottom, as they appear in the 'Conditional Formatting Manager':

Green row if the Organization has TRUE in any record:: =MATCH($A2&TRUE(),$A$2:$A$7&$H$2:$H$7,0)
Red row when column H is false:: =$H2=FALSE
Green row when column H is TRUE formula:: =$H2