Forum Discussion

Golf Link's avatar
Golf Link
Copper Contributor
Nov 07, 2017

Conditional Formatting - formula for adjacent cells

Hello Community,   I am wanting to assign Conditional Formatting to a column adjacent to another column that already has conditional formatting assigned. The 2 columns are divided into 2 Groups (G...
Formulas & Functions
Need Help
Golf Link's avatar
Golf Link
Copper Contributor
Nov 09, 2017

Thank you Sergei for your suggested solution.

The rule works well for retrieving the largest in range $C$2:$C$9 and also the largest in range $C$11:$C$18.

 

I am instead wanting to retrieve just the one largest from the entire range $C$2:$C$18.

 

I unsuccessfully attempted to  modifying your rule with the following:

=AGGREGATE(14,6,($C$2:$C$18)*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$2:$E$9,1),$E$2:$E$9,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$11:$E$18,1),$E$11:$E$18,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$2:$E$9,2),$E$2:$E$9,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$11:$E$18,2),$E$11:$E$18,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$2:$E$9,3),$E$2:$E$9,0)))*
($C$2:$C$18<>INDEX($C$2:$C$18,MATCH(LARGE($E$11:$E$18,3),$E$11:$E$18,0))),1)

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 09, 2017

Hi Golf,

 

If as I assumed when you shall to check each range separately, formula will be

=AGGREGATE(14,6,($C$2:$C$18)*
   ($C$2:$C$18<>INDEX($C$2:$C$9,MATCH(LARGE($E$2:$E$9,1),$E$2:$E$9,0)))*
   ($C$2:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,1),$E$11:$E$18,0)))*
   ($C$2:$C$18<>INDEX($C$2:$C$9,MATCH(LARGE($E$2:$E$9,2),$E$2:$E$9,0)))*
   ($C$2:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,2),$E$11:$E$18,0)))*
   ($C$2:$C$18<>INDEX($C$2:$C$9,MATCH(LARGE($E$2:$E$9,3),$E$2:$E$9,0)))*
   ($C$2:$C$18<>INDEX($C$11:$C$18,MATCH(LARGE($E$11:$E$18,3),$E$11:$E$18,0))),1)

Alternatively you may compare not the numbers but exclude row there they are, when

=AGGREGATE(14,6,($C$2:$C$18)*
   ((ROW($C$2:$C$18)-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,1),$E$2:$E$9,0))*
   ((ROW($C$2:$C$18)-ROW($C$2)+1)<>COUNT($C$2:$C$11)+MATCH(LARGE($E$11:$E$18,1),$E$11:$E$18,0))*
   ((ROW($C$2:$C$18)-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,2),$E$2:$E$9,0))*
   ((ROW($C$2:$C$18)-ROW($C$2)+1)<>COUNT($C$2:$C$11)+MATCH(LARGE($E$11:$E$18,2),$E$11:$E$18,0))*
   ((ROW($C$2:$C$18)-ROW($C$2)+1)<>MATCH(LARGE($E$2:$E$9,3),$E$2:$E$9,0))*
   ((ROW($C$2:$C$18)-ROW($C$2)+1)<>COUNT($C$2:$C$11)+MATCH(LARGE($E$11:$E$18,3),$E$11:$E$18,0)),1)

Both formulas are in attached file, first one is applied to conditional formatting.

Resources