Conditional formatting - Find common material
 

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

Conditional formatting - Find common material
 

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" wrote in message
ups.com...
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

Conditional formatting - Find common material
 

thats rite..

Conditional formatting - Find common material
 

OK.......

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

Biff

"kuansheng" wrote in message
oups.com...
thats rite..

Conditional formatting - Find common material
 

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.

Conditional formatting - Find common material
 

"kuansheng" wrote in message
ups.com...
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

Conditional formatting - Find common material
 

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

Conditional formatting - Find common material
 

Try this:

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

Biff

"kuansheng" wrote in message
oups.com...
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

Conditional formatting - Find common material
 

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

Conditional formatting - Find common material
 

"kuansheng" wrote in message
oups.com...
THanks a million that is what i am looking for. Could you guide me how
this formula works?

Su

=SUMPRODUCT(--(MOD(COLUMN(I4:U4),2)=1),--(I4:U40))=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