conditional formatting for key-value columns

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

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