Display maximum value of matching values in a different column
 

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

Display maximum value of matching values in a different column
 

Use a conditional formatting formula of

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

"Mally" wrote in message
...
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

Display maximum value of matching values in a different column
 

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" wrote:

Use a conditional formatting formula of

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

"Mally" wrote in message
...
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

Display maximum value of matching values in a different column
 

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" wrote in message
...
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" wrote:

Use a conditional formatting formula of

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

"Mally" wrote in message
...
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

Display maximum value of matching values in a different column
 

Whoo hoo!

Thanks bob. Thats great.

Mally

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

Use a conditional formatting formula of

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

"Mally" wrote in message
...
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

Display maximum value of matching values in a different column
 

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" wrote:

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" wrote in message
...
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" wrote:

Use a conditional formatting formula of

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

"Mally" wrote in message
...
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

Display maximum value of matching values in a different column
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

Use a conditional formatting formula of

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

"Mally" wrote in message
...
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

Display maximum value of matching values in a different column
 

Now that it's in an array it works.

Thanks for your help Bob.

Kind Regards

Mally

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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" wrote:

Use a conditional formatting formula of

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

"Mally" wrote in message
...
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