Microsoft excel formula for highlighting unique cells 1

[Dausa67]

I am not sure if this can even be done but you can do everything else with excel so I figured I would ask.

I have a large spread sheet that I want to easily identify part numbers that have multiple vendor numbers with stock on hand. If a part number has multiple vendor numbers and only one of them has stock on hand then I do not want to highlight the cell. I hope this makes sense.

I am using excel 2003. Below is an example of what I would like to do.

Part #     Vendor #    On Hand QTY
01APPO6    M15570-12[COLOR=red]       6[/color]
           M15576-02[COLOR=red]      400 [/color]
 
01APP07    M15571-04       0  (no highlight needed)
           M15572-04       12 (no highlight needed)

01APPO1    M15568-12[COLOR=red]      145  [/color]
           M15564-02[COLOR=red]      1200 [/color]

Clay

 

[SkipVought]

Clay,

No VBA required!

I am using Named Ranges with these heading value...
[tt]
Part ID
Vendor ID
On Hand Qty
[/tt]
Also you table must look like this in order to be able to properly analyze the data...
[tt]
Part ID Vendor ID On Hand Qty
01APPO6 M15570-12 6
01APPO6 M15576-02 400
01APP07 M15571-04 0
01APP07 M15572-04 12
01APPO1 M15568-12 145
01APPO1 M15564-02 1200
[tt]
In CF Formula is...
[tt]
=COUNTIF(Part_ID,A2)=SUMPRODUCT((Part_ID=A2)*(On_Hand_Qty>0))
[/tt]
Logic: compare the occurences of Part ID to the occurences of OHQ>0 for that Part ID

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dausa67]

ok. thanks for the advise. I can not sort or re-format the report. all the fields have to be able to stay where they are.

Clay

 

[SkipVought]

You can use conditional formatting on the Repeated values in the first column. But the values must be in ALL the cell with data in that column.

I only renamed the columns in my example to make it clear how the Named ranges work. You can use your names, but then the Part # range name is just Part, for instance.

Empty rows are NEVER a good idea in a table. They make many of Excel's features much more difficult to use. I'd rather not shoot myself in the foot. And I educate my users to also treat themselves with equal deference as well.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}