conditional formatting for key-value columns

[kang]

there are two columns.
the first column is the key column, and the second one is the value column.
I want to conditional-format the rows which is the same key with
different values.
Help me.

 

[Max]

Assuming there's not more than 3 different keys to CF, here's how ..

Select cols A and B (A1 active), then apply CF using "Formula Is"

Cond1: =$A1="Key1"
Format to taste

Cond2: =$A1="Key2"
Format to taste

Cond3: =$A1="Key3"
Format to taste

Ok out

Adapt the 3 keys to suit, viz: Key1, Key2, Key3

 

[kang]

won't work.

 

[kang]

abc xyz
bcd yz
bcd yxy
abc xyz

I expect the example above CFs two rows(bcd-yz,bcd-yxy) because the two
have the same key(bcd) and diffreent values(yz,yxy)
whereas the abc's have the same value(xyz)

 

[Max]

Ah, you should have given the details earlier

One play to try ..

Assume data to be conditionally formatted is within A2:B10

Select A2:B10 (A2 active), then apply CF using Formula is:

Cond1:
=SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10=$B2)*($A$2:$A$10<>"")*($B$2:$B$10<>""))=1
Format to taste

Adapt the ranges to suit ..

 

[kang]

key val
a x 1
b y 2
c z 1
d x 1
a y 1
b y 2

the formular
(=SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10=$B2)*($A$2:$A$10<>"")*($B$2:$B$10<>"")))
for c and d gives 1

don't you think the values for the keys(c,d) should be different from a?

 

[Max]

don't you think the values for the keys(c,d) should be different from a?

No, if the rules/specs mentioned in your earlier posting still apply.

If you have the below as the source data within the CF'd range: A2:B10

a x
b y
c z
d x
a y
b y

all lines except the 2 lines with: b y
would be correctly formatted by the CF

 

[kang]

ok help me again.
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

 

[Max]

Suggest you put in your new query as a fresh, new posting.
I'm out of ideas, sorry.