EXCEL Conditional Formatting: Define a row range in which to identify the highest value in a column

Question

I have an Excel spreadsheet consisting of groups of rows. Each row group is defined by the matching values in Column A. For example:

................A....................B    
1..........Group Name 1............Data1  
2..........Group Name 1............Data2  
3..........Group Name 1............Data3  
4..........Group Name 1............Data4  
5..........Group Name 2............Data5  
6..........Group Name 2............Data6  
7..........Group Name 2............Data7  
8..........Group Name 3............Data8  
9..........Group Name 3............Data9  
10.........Group Name 4............Data10  
11.........Group Name 4............Data11  
12.........Group Name 4............Data12  

I'd like to use Conditional Formatting to highlight the highest value in Column B within each row group, but I'm not able to determine a formula to identify the range of each row group.

How would this be done?

Answer 1

Admitting that column B contain numeric data you can write this formula in conditional formatting:

=B1=MAX(IF(A1=$A$1:$A$12;$B$1:$B$12;0))

depending on your regional settings you may need to replace ";" by ","

Answer 2

Here is a standard (non-array) formula that produces a similar pseudo-MAXIF() functionality.

=$B1=MAX(INDEX($B$1:$B$12*($A$1:$A$12=$A1),,))

I typically lock the columns in a CF rule where the rows are allowed to 'float' but this is not absolutely necessary if you are applying the CF rule to a single column.