Array formula needs to handle a case where no value appears in a cell

[Motox]

Hi,

I have a spreadsheet that tracks delivery of equipment. Each item of equipment is made up of many parts and has a delivery date for each part.

I have an array formula that I have been using to find the latest delivery date for a piece of equipment (the latest delivery date for a part for that equipment). The formula is:

=MAX(INDEX(IF($A$2:$A$30000=G2,$B$2:$B$30000),0))


The way it works is:

If the value in G2 exists in the range A2:A30000, return it’s corresponding delivery date from the range B2:B30000, otherwise return 0.


I have since found that some parts have no delivery date as yet. My formula only looks for the latest date of parts that have arrived (or forecast dates). I need it to recognise that if an equipment item has a part with no delivery date (a blank cell in the delivery date range B2:B30000) then no delivery date should appear for the equipment. Otherwise my delivery date for the equipment will be misleading because there are parts required that don’t even have a forecast delivery date assigned yet that are missed by my formula. (A forecast date will be assigned to these items as a seperate excercise by purchasing dept.)

What changes need to be made to my formula to achieve this?

Thanks very much

DFB

[teylyn]

Hello,

try

=IF(LOOKUP(2,1/($A$2:$A$15=G2)*($B$2:$B$15=""))>=1,"",MAX(INDEX(IF($A$2:$A$15=G2,$B$2:$B$15,""),0)))

For testing purposes, I reduced the ranges from 30,000 to 15 rows. Ramp them back up, if you need.

I would hate to run this on 30,000 rows, though. You should try to use dynamic ranges that grow and shrink with the data, so you only process populated cells. That will speed up your spreadsheet considerably.

cheers,

[Motox]

Hi,

Thanks very much.

The formula is actually perfomed in a loop inside a VBA macro so looks like - IF(LOOKUP(2,1/(R2C1:R30000C1=RC[-2])*(R2C2:R30000C2=""""))>=1,"""",MAX(INDEX(IF(R2C1:R30000C1=RC[-2],R2C2:R30000C2,""""),0))) - which makes it very hard for me to make sense of (R1C1 style instead of $A$2).

BTW, the G2 in the formula I first submitted should have been F2.

I tried the code and it only returns a value for the first row. Not sure why? Normally i would have thought that the F2 should be $F$2, perhaps, but the R1C1 style baffles me.

Here is the part of the code concerned:

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

Counter = Counter + 1

Thanks again

DFB

[Motox]

Hi,

Found a typo in my formula. It now works but shows a blank cell for individual parts with no dates assigned, but still returns the latest date overall for the equipment.
There are many repeating equipment numbers with various delivery dates (representing parts of the equipment). I need the overall result to return a blank cell if for example, there are 12 parts with the same equipment number and 11 have delivery dates and one does not.

Thanks very much

DFB

[teylyn]

Post a workbook with some context and an expected result. Working just off the formula I don't really understand what you mean by "but still returns the latest date overall for the equipment. "