Format entries based on 2 metrics

[ShyamExcel]

Hi Excel Wizards,


Probably a easy one!


I have a situation such with 3 columns (Name, metric1, metric2) and the goal is to pick one row based on metric1 and metric2 for each of the names. There are multiple entries for each name. I've pasted a sample table below.


So for each name, I'd like to highlight a row which has the highest metric2 and if metric2 is zero or same for all entries for that name, highlight row with the highest metric1 for that name.


So for 'AA' row 3 should be highlighted and for BB, the only row should be highlighted, for CC, it will be row 6.


Can anyone give me a easy way to do this? i have over 60k rows to format this way.


is there are conditional formatting formula we can enter for this case?


Thanks a lot!


Shyam


Name Metric1 Metric2

AA 2% 1%

AA 1.50% 1.20% <------

BB 2% 2.24% <------

CC 1% 1.00%

CC 1.20% 1.00% <------

DD 2.24% 2% <------

EE 1% 1%

EE 1.50% 1.20%

EE 2% 2.24% <------

EE 1% 2%

FF 1.20% 1% <------

 

[keymaster]

interesting question. Although you can set up the whole rule in CF, using a helper column would make the formula writing simpler.


Assuming your data is in A2:C12 (A1:C1 contains headings), in D2 write,


=A2&IF(C2=SUMPRODUCT( MAX(($C$2:$C$12)* ($A$2:$A$12=A2))) ,IF(AND(C2<>0,COUNTIFS($C$2:$C$12,C2,$A$2:$A$12,A2)=1),1,

IF(B2=SUMPRODUCT(MAX(($B$2:$B$12)*($A$2:$A$12=A2))),1,0)),0)


And drag it down. This would generate values like AA0, AA1, BB1....

The cells with AA1, BB1 etc. are the ones we need to highlight. Then you can use simple CF rule to highlight.


How does this formula work?


C2=SUMPRODUCT( MAX(($C$2:$C$12)* ($A$2:$A$12=A2))) portion checks if Metric 2 is highest for that Name (a kind of MAXIF)


If that is true, we check the 2nd condition

which is AND(C2<>0,COUNTIFS($C$2:$C$12,C2,$A$2:$A$12,A2)=1)


to see if C2 (Metric 2) is not zero

AND

if C2 is unique (count of metric 2 value for that name should be 1


Then, we append 1 to A2 (name)

Else, we check metric 1

B2=SUMPRODUCT(MAX(($B$2:$B$12)*($A$2:$A$12=A2))) using similar formula as metric2.


If Metric 1 is highest, then we append 1.

In all other cases, we append 0.


See the sample file attached to understand how this works.


http://img.chandoo.org/playground/highlight-metrics-shyam.xlsx

 

[ShyamExcel]

You are a rock star!


Take a bow!