Conditional formatting - Find common material

[kuansheng]

Hi Guys,

What i am trying to do is to to determine the common material that is
used among different model od product in a product family. I have the
column C the various part number for the product family. Each product
model is made up of different combination of the parts.

In I3:U3 i have the model number for each product. Under each are the
combination of various part that make up each model. What i need to do
is in column G conditional formatiing that if all the different model
use a particular part (part number). The respective cell in column in
the row will be color. This will help me to determine what are the
parts that are common to all the product. Pls see below. Many thanks.

Column C Column G Column I .........................Column U
Part no Common Product 1 Product 2 Product 3 Product 4
12-1234-56 no color 1 4 0 6
13-2345-45 color 2 3 2 2
14-1234-56 no color 0 2 4 2
14-1234-56 no color 0 2 2 2

 

[Biff]

Hi!

It looks like if a particular row from I:U does not contain a zero then
apply the color. Is that correct?

Biff

 

[kuansheng]

thats rite..

 

[Biff]

OK.......

Select the range of cells in column G. I'll assume that's G3:G10.
Goto Format>Conditional Formatting
Formula is: =COUNTIF($I3:$U3,0)=0
Click the Format button
Select the desired style(s)
OK out

Biff

 

[kuansheng]

What i need is that if a particular row from I:U all the cell must not
contain zero or blank then color. I was wrong earlier.

 

[Biff]

What i need is that if a particular row from I:U all the cell must not
contain zero or blank then color. I was wrong earlier.

OK, change the formula to:

=AND(COUNTIF($I3:$U3,0)=0,COUNTBLANK($I3:$U3)=0)

Biff

 

[kuansheng]

This didnt work. In column I:U, column I, K, M, O, Q, S and U. I will
color cell in the respective row of column G. only if in all cell of a
row of I, K, M, O, Q, S and U contain a numeric value except a zero or
blank. What i meant is that in for example in row 4. All the cell I4,
K4, M4, O4, Q4, S4 and U4 all must contain a numeric value except a
zero or blank, then the respective cell in column G (G4) will be color.
This is used to indicate that all the different product uses this part
thus is common to all. Sorry for all the trouble i have caused. Thanks

 

[Biff]

Try this:

=SUMPRODUCT(--(MOD(COLUMN(I4:U4),2)=1),--(I4:U4>0))=7

Biff

 

[kuansheng]

THanks a million that is what i am looking for. Could you guide me how
this formula works?

 

[Biff]

THanks a million that is what i am looking for. Could you guide me how
this formula works?

Sure:

=SUMPRODUCT(--(MOD(COLUMN(I4:U4),2)=1),--(I4:U4>0))=7

Since the range you're interested in is every other column starting in
column I through column U, the MOD function checks the column number and
makes sure only the defined columns you want are included in the
calculation.

There are a total of 7 columns that fit the criteria so we need to check
that the total number of entries that meet the criteria equals 7.

Biff