conditional formatting for key-value columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kang - 21 Aug 2007 11:32 GMT

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.

Reply to this Message

Max - 21 Aug 2007 12:10 GMT

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

Signature

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

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

Reply to this Message

kang - 21 Aug 2007 14:33 GMT

won't work.

> Assuming there's not more than 3 different keys to CF, here's how ..
>
[quoted text clipped - 12 lines]
>
> Adapt the 3 keys to suit, viz: Key1, Key2, Key3

Reply to this Message

kang - 21 Aug 2007 14:49 GMT

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)

> Assuming there's not more than 3 different keys to CF, here's how ..
>
[quoted text clipped - 12 lines]
>
> Adapt the 3 keys to suit, viz: Key1, Key2, Key3

Reply to this Message

Max - 21 Aug 2007 15:06 GMT

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

Signature

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

> abc xyz
> bcd yz
[quoted text clipped - 4 lines]
> have the same key(bcd) and different values(yz,yxy)
> whereas the abc's have the same value(xyz)

Reply to this Message

kang - 26 Aug 2007 05:35 GMT

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?

> Ah, you should have given the details earlier
>
[quoted text clipped - 9 lines]
>
> Adapt the ranges to suit ..

Reply to this Message

Max - 26 Aug 2007 08:31 GMT

> 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

Signature

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

> key val
> a x 1
[quoted text clipped - 9 lines]
>
> don't you think the values for the keys(c,d) should be different from a?

Reply to this Message

kang - 27 Aug 2007 09:19 GMT

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

>> don't you think the values for the keys(c,d) should be different from a?
>
[quoted text clipped - 11 lines]
> all lines except the 2 lines with: b y
> would be correctly formatted by the CF

Reply to this Message

Max - 27 Aug 2007 17:35 GMT

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

Signature

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

Reply to this Message

Rate this thread: