1

I am using excel 2016, where I need to do some coloring based on group and a value in a particular column.

My input record is given below

enter image description here

My requirement is that within a group(Grouping is done based on COLA and COLd), if COLC have a value "CP", then I need to highlight all the rows within that group.

My expected output is given below

enter image description here

user1035973
  • 11

1 Answers1

1

From what I best understand your question, I suggest a solution based on a Helper column. A solution without Helper column might also be possible.

In this example sample data is in Cells D2:J13, D being the header row.

At the end of your data, create a Helper Column and put the following Formula in K3 and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The Formula shall now be enclosed in curly braces to indicate that it's an Array Formula. All it does it checks for CP within a group and if found marks all Cells of that group in Helper Column as 1 else 0.

=IF(SUM(IF(IF($D$3:$D$13=D3,$F$3:$F$13,$D$1)="CP",1,0))>0,1,0)

In above formula there's a constant reference of D1 which is a blank cell somewhere outside of the data.

Select Cell D3, go to Conditional Formatting. Select New Rule, Use a Formula to determine which cells to format.

Put the formula =$K3=1 and select the background fill color of your choice.

Now use the Format Painter and apply the conditional formatting to all of your applicable cells.

enter image description here

enter image description here

patkim
  • 5,455
  • I tried the above formula, but with as per my input data which I have given =IF(SUM(IF(IF($A$3:$A$10001=A3,$C$3:$C$13,$A$1)="CP",1,0))>0,1,0), but its giving me error telling that that this formula is not correct. Also please note that I have around 400 thousand records in my excel for this formating – user1035973 May 14 '19 at 16:30
  • You might have ; as list separator in your regional settings. If so try the formula with ; instead of , . Also do not forget to create an array formula by pressing CTRL + SHIFT + ENTER from within the formula bar. Also you need to select $C$3:$C$10001 in the formula not just C3:C13 – patkim May 14 '19 at 16:43