Display maximum value of matching values in a different column

  • Thread starter Thread starter Mally
  • Start date Start date
M

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
 
Use a conditional formatting formula of

=AND(COUNTIF($A:$A,A1)>1,COUNTIF($A2:$A$20,A1)=0)
 
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
 
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)
 
Whoo hoo!

Thanks bob. Thats great.

Mally

Bob Phillips said:
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)
Click to expand...
 
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
 
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)
 
Back
Top