Display maximum value of matching values in a different column

[Mally]

Hi

I want to highlight the maximum value if values in a correponding column
match.

e.g below is an example of a spreadsheet

A B
1 a 1
2 f 3
3 f 4
4 d 2
5 e 2
6 e 5
7 e 1

In this example
cell reference B3 will be highlighted because A2 and A3 are the same
cell reference B6 will be highlighted because A5, A6 and A7 are the same

Thankyou

 

[Bob Phillips]

Use a conditional formatting formula of

=AND(COUNTIF($A:$A,A1)>1,COUNTIF($A2:$A$20,A1)=0)

 

[Mally]

Thanks Bob

I copied this down a column but unfortunatley it didn't work where there was
more than 2 matching numbers in the first column as in the example below. A7
showed TRUE where it should have been A6

 

[Bob Phillips]

Sorry, I mis-read the spev.

Use this formula

=AND(COUNTIF($A$1:$A$20,A1)>1,B1=MAX(IF($A$1:$A$20=A1,$B$1:$B$20)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mally]

Whoo hoo!

Thanks bob. Thats great.

Mally

Sorry, I mis-read the spev.

Use this formula

=AND(COUNTIF($A$1:$A$20,A1)>1,B1=MAX(IF($A$1:$A$20=A1,$B$1:$B$20)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mally]

Hi again

Oops! Again it didn't work exactly how I need it. In the example below

Cell B3 should be TRUE because A2 to A3 both = "f" and B3 is the highest
value

AND

Cell B6 should be TRUE because A5 to A9 all = "e" and B6 is the highest
value

etc...

A B
1 a 1
2 f 3
3 f 4
4 d 2
5 e 2
6 e 5
7 e 1
8 e 2
9 e 4

I hope this makes sense

Mally

 

[Bob Phillips]

That is exactly the results I get. It was intended for use in conditional
formatting. If you put it in a cell, you must array-enter it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mally]

Now that it's in an array it works.

Thanks for your help Bob.

Kind Regards

Mally