If statement with variable length range

[SleepyBat]

help.xlsx

So i have a large data set 120k rows its a bill of materials i need to look at options and see if you have all the CAD for them the problme is all the options are different length so i have to change the range every time i thinking there must be a better way to do this. see the attached file

This is the code i have at the minute

Please Login or Register  to view this content.

[AliGW]

Maybe this?

=IF(F4="Has CAD",IF(COUNTIF(B5:B6,"CAD Not Found")>0,"Some CAD Missing","All CAD available"),IF(COUNTIF(B5:B6,"CAD Not Found")>0,"Some CAD Missing","All Part level CAD available but option is missing"))

[Glenn Kennedy]

Maybe this, in row 3 copied down:

=LET(A,B4:INDEX(B4:B15,AGGREGATE(15,6,ROW(A4:A15)-ROW(A4)+1/(A4:A15<>A4),1)-1),B,COUNTIF(A,"CAD Not Found"),IF(F3="-","",IF(F3="Has CAD",IF(B,"Some CAD Missing","All CAD available"),IF(B,"Some CAD Missing","All Part level CAD available but option is missing"))))

[6StringJazzer]

Welcome to the Forum SleepyBat!

You did not say how you want to determine the range but it looks like you want to consider all of the rows that follow each Level 1 item, until you get to the next Level 1 item.

Column A is text, and I recommend that it should be numbers. But I didn't change it, so my formula uses text.

You have to add a fake Level 1 at the end of the list for this to work. This formula is good for up to 200K rows.

Please Login or Register  to view this content.

See attached.

[SleepyBat]

@6StringJazzer

That worked thank you.

[Glenn Kennedy]

It might have been nice if you had even acknowledged the existence of your other two helpers.

[AliGW]

Yes, would have been polite to do so.