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

  • Thread starter Thread starter kang
  • Start date Start date
K

kang

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
 
Dave Peterson said:
I see you have an active thread in .newusers.

I'll bow out of this thread.
Click to expand...

Dave, don't <g>. Think I've run dry of ideas for the OP over in .newusers
(in his repeat posting).

---
 
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.
 
Back
Top