find all key-value pairs if there is key with different value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kang - 27 Aug 2007 05:17 GMT

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

Reply to this Message

Dave Peterson - 27 Aug 2007 13:01 GMT

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

> I want to find all key-value pairs if there is key with different value.
> in the case above
[quoted text clipped - 21 lines]
> the format formular is
> =SUMPRODUCT(($A$1:$A$10=$A1)*($B$1:$B$10=$B1)*($A$1:$A$10<>"")*($B$1:$B$10<>""))=1

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 27 Aug 2007 13:24 GMT

I see you have an active thread in .newusers.

I'll bow out of this thread.

> I think this does what you want.
>
[quoted text clipped - 43 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Max - 28 Aug 2007 00:06 GMT

>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).

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Dave Peterson - 28 Aug 2007 02:14 GMT

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.

> >I see you have an active thread in .newusers.
> >
[quoted text clipped - 8 lines]
> xdemechanik
> ---

Signature

Dave Peterson

Reply to this Message

Rate this thread: