I want to find all key-value pairs if there is key with different value.
in the case above
A 1
A 1
B 2
B 2
B 3
C 2
C 4
the first
A 1 : should not be formatted because all the values with A key are 1.
A 1 : should not be formatted with the same reason above
B 2 : should be formatted because the values for the Key B is not
uniformly equal
B 2 : should be formatted because the values for the Key B is not
uniformly equal even though there exists the same value with the same key
B 3 : should be formatted because the values for the Key B is not
uniformly equal
C 2 : should be formatted because the values for the Key C is not
uniformly equal
C 4 : should be formatted because the values for the Key C is not
uniformly equal

the format formular is
=SUMPRODUCT(($A$1:$A$10=$A1)*($B$1:$B$10=$B1)*($A$ 1:$A$10<>"")*($B$1:$B$10<>""))=1

I think this does what you want.

Select the range you want to give that format|Conditional format. With A1 the
activecell, use this formula:

=COUNTIF($A$1:$A$10,A1)=SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

kang wrote:
>
> I want to find all key-value pairs if there is key with different value.
> in the case above
> A 1
> A 1
> B 2
> B 2
> B 3
> C 2
> C 4
> the first
> A 1 : should not be formatted because all the values with A key are 1.
> A 1 : should not be formatted with the same reason above
> B 2 : should be formatted because the values for the Key B is not
> uniformly equal
> B 2 : should be formatted because the values for the Key B is not
> uniformly equal even though there exists the same value with the same key
> B 3 : should be formatted because the values for the Key B is not
> uniformly equal
> C 2 : should be formatted because the values for the Key C is not
> uniformly equal
> C 4 : should be formatted because the values for the Key C is not
> uniformly equal
>
> the format formular is
> =SUMPRODUCT(($A$1:$A$10=$A1)*($B$1:$B$10=$B1)*($A$ 1:$A$10<>"")*($B$1:$B$10<>""))=1

--

Dave Peterson

I see you have an active thread in .newusers.

I'll bow out of this thread.

Dave Peterson wrote:
>
> I think this does what you want.
>
> Select the range you want to give that format|Conditional format. With A1 the
> activecell, use this formula:
>
> =COUNTIF($A$1:$A$10,A1)=SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1))
>
> Adjust the ranges to match--but you can't use whole columns (except in xl2007).
>
> =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> kang wrote:
> >
> > I want to find all key-value pairs if there is key with different value.
> > in the case above
> > A 1
> > A 1
> > B 2
> > B 2
> > B 3
> > C 2
> > C 4
> > the first
> > A 1 : should not be formatted because all the values with A key are 1.
> > A 1 : should not be formatted with the same reason above
> > B 2 : should be formatted because the values for the Key B is not
> > uniformly equal
> > B 2 : should be formatted because the values for the Key B is not
> > uniformly equal even though there exists the same value with the same key
> > B 3 : should be formatted because the values for the Key B is not
> > uniformly equal
> > C 2 : should be formatted because the values for the Key C is not
> > uniformly equal
> > C 4 : should be formatted because the values for the Key C is not
> > uniformly equal
> >
> > the format formular is
> > =SUMPRODUCT(($A$1:$A$10=$A1)*($B$1:$B$10=$B1)*($A$ 1:$A$10<>"")*($B$1:$B$10<>""))=1
>
> --
>
> Dave Peterson

--

Dave Peterson

"Dave Peterson" wrote:
>I see you have an active thread in .newusers.
>
> I'll bow out of this thread.

Dave, don't <g>. Think I've run dry of ideas for the OP over in .newusers
(in his repeat posting).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I really hate wasting my time on multiposted messages.

Most of the time, similar suggestions are posted and it's just a waste for both
the responder and the OP.

Max wrote:
>
> "Dave Peterson" wrote:
> >I see you have an active thread in .newusers.
> >
> > I'll bow out of this thread.
>
> Dave, don't <g>. Think I've run dry of ideas for the OP over in .newusers
> (in his repeat posting).
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

--

Dave Peterson