Array formula to return Max values or zero

Hi,

I have a spreadsheet that takes delivery dates provided by the expediting department and I use this to advise the assembly department of when to expect delivery of all parts so that they can assemble the equipment.

The part numbers below all roll up to the Equipment Number 1314-HO-036. When my code detects 1314-HO-036 in the Equipment Number column it looks for the date and returns the latest date for all items( 10-Feb-12). (As the latest date of all parts indicates when the guys can plan to assemble the equipment).

Please Note: This is not the column layout found in my spreadsheet, just an example.

Part No Del Date Eqp Number
1314-HO-036-14 10-Feb-12 1314-HO-036
1314-HO-036-15 02-Feb-12 1314-HO-036
1314-HO-036-16 02-Feb-12 1314-HO-036
1314-HO-036-17 1314-HO-036
1314-HO-036-18 02-Feb-12 1314-HO-036
1314-HO-036-19 02-Feb-12 1314-HO-036
1314-HO-036-20 02-Feb-12 1314-HO-036
1314-HO-036-21 02-Feb-12 1314-HO-036





The part of my VBA code that does this is:

Range("F1").Select
    ActiveCell.FormulaR1C1 = "Latest AOS Date"
    Counter = 2
    Do While IsEmpty(Cells(Counter, 4)) = False
        Cells(Counter, 6).FormulaArray = "=MAX(INDEX(IF(R2C1:R30000C1=RC[-2],R2C2:R30000C2),0))"        
        Counter = Counter + 1       
    Loop

I have since realised that in the case where no date has been assigned yet for a part (1314-HO-036-17 in the table above) I need to have no date returned for the overall Equipment Number 1314-HO-036. If a date is returned this appears in my report and gives the false impression that all items will be delivered by this date. No date indicates that a firm delivery date is not yet known.

The overall result returned by the code should show no delivery date for equipment items that lack a delivery date for one or more parts (1314-HO-036 below) and the latest delivery date for equipment items that have a delivery date for all parts . Below is an example of the results I should get back where there is no date returned for 1314-HO-036 because one of its parts did not have a delivery date


1314-SQ-031 02-Feb-12
1314-HO-036
1314-BP-056 06-Mar-12
1314-LD-036 10-Feb-12
1314-CF-024 02-Jun-12


I know this is really confusing. Basically, if all parts for a given equipment number (1314-HO-036, for example) have a date assigned then I need to return the latest of these dates as the delivery date for 1314-HO-036.

If there are one or more parts of 1314-HO-036 (such as 1314-HO-036-17) that do not have a delivery date then my delivery date for 1314-HO-036 needs to be left blank.

How can I get my code to do this?

Thanks very much

Dave

Something like

Cells(Counter, 6).FormulaArray = "=IF(SUMPRODUCT((R2C1:R30000C1=RC[-2])*(R2C2:R30000C2=""""))>0,"""",MAX(IF(R2C1:R30000C1=RC[-2],R2C2:R30000C2,0)))"

Just as an arrayformula in a cell:

PHP Code: 

=TEXT(MAX((C1:C8="1314-HO-038")*(B1:B8<>"")*(B1:B8)),"dd-mm-yyyy") 


in VBA:

cells(6,1)= [text(max(if(C1:C100="1314-HO-038",if(B1:B100="","",B1:B100),"")),"dd-mm-yyyy")]

@snb
That's no different from the original formula. The idea is that if any of the delivery dates for a part are blank, the result should be blank. MAX already ignores blank cells anyway.