Aggregate/Indirect Formula Breaks Down After 3 or More Conditions

[meseleto]

Hello all,

I have a large spreadsheet with almost all orders containing one or two quantities/items. I thought I had a formula that was working to pull information from two worksheet tabs but a coworker noticed it breaks down at 3 or more unique items. This occurs most often on the weekends and today was the first day we started using it so now it is becoming a problem. Is there a better way to write these formulas? The absolute maximum unique occurences would be 9 but again, nearly all are 3 or fewer but it would be great if something would work for that many.

Thanks,

[Bo_Ry]

Maybe try at
H3
=IF(I3="","",VLOOKUP(I3,ZSD_ORDERS_ZSTO!$F$2:$H$9,3,0))

I3
=IFERROR(INDEX(ZSD_ORDERS_ZSTO!$F:$F,AGGREGATE(15,6,ROW(ZSD_ORDERS_ZSTO!$F$2:$F$9)/(ZSD_ORDERS_ZSTO!$A$2:$A$9=LOOKUP("zz",$D$3:$D3)),ROWS(I$3:I3))), "")

[meseleto]

Thank you for the help Bo_Ry. It seems to work in this specific representation on my attachment but not in my original spreadsheet that has 1000's of rows. Is there an easy way to make it more adaptable or would I have to manually alter the ranges?

[Bo_Ry]

Please try

H3
=IF(I3="","",VLOOKUP(I3,ZSD_ORDERS_ZSTO!$F$2:$H$9999,3,0))

I3
=IFERROR(INDEX(ZSD_ORDERS_ZSTO!$F:$F,AGGREGATE(15,6,ROW(ZSD_ORDERS_ZSTO!$F$2:$F$9999)/(ZSD_ORDERS_ZSTO!$A$2:$A$9999=LOOKUP("zz",$D$3:$D3)),ROWS(I$3:I3))), "")

[jason.b75]

Will all same Delivery #'s be in consecutive rows? If so then this might be more efficient than aggregate.

Please Login or Register  to view this content.

[meseleto]

The VLOOKUP won't work on quantities/upc because there isn't just once occurrence of those columns/row values. The same code can appear multiple times on the same day for different quantities.

[meseleto]

This gets me closer. The unique delivery number will always end with a "3." So D$3, D$13, D$23, etc. The possible matches in columns H and I will correspond to this particular row. So H$3-H$11 and I$3-I$11 will be tied to D$3, H$13-H$21 and I$3-I$21 will be tied to D$13...so on and so forth.

If I manually adjust these in the formula above, it works for me. Is there a way to automate that now that you know how the delivery #'s will run?

[jason.b75]

Does my suggestion in post #5 get you anywhere?

[meseleto]

Does my suggestion in post #5 get you anywhere?

Yes.

Sorry, my post on #7 was referring to your post #5. I see I should have clicked on reply with quote instead of just reply.

[jason.b75]

This should do it as long as D4:D12,D14:D22, etc are empty. Nullstrings (formulas that return "") will cause it to fail.

Please Login or Register  to view this content.

It would be more stable if your Delivery #'s were not formatted as text.

If they were in numeric format

Please Login or Register  to view this content.

would be able to pick them out from the nullstrings.

[meseleto]

I have attached another spreadsheet with more data. I may not be getting your suggestions, sorry about that.

[jason.b75]

I'm not seeing the new file with the additional data. Which post did you attach it to?

[meseleto]

How about now?

[Bo_Ry]

Please try at
H3
=IF(I3="","",LOOKUP(2,1/(ZSD_ORDERS_ZSTO!$A$2:$A$9999=LOOKUP(2,1/LEN(D$3:D3),D$3:D3))/(ZSD_ORDERS_ZSTO!$F$2:$F$9999=I3),ZSD_ORDERS_ZSTO!$H$2:$H$9999))

I3
=IFERROR(INDEX(ZSD_ORDERS_ZSTO!$F:$F,AGGREGATE(15,6,ROW(ZSD_ORDERS_ZSTO!$F$2:$F$9999)/(ZSD_ORDERS_ZSTO!$A$2:$A$9999=LOOKUP(2,1/LEN(D$3:D3),D$3:D3)),ROWS(I$2:I3)-MATCH(2,INDEX(1/LEN(D$3:D3),)))), "")

[jason.b75]

This should be more efficient that the aggregate arrays, but it relies on 2 strict conditions.

Condition 1:- All identical delivery #'s in the ZSD_ORDERS sheet are in consecutive rows.
Condition 2:- No item will ever be recorded more than once against a single delivery #.

In I3

Please Login or Register  to view this content.

In H3

Please Login or Register  to view this content.

[meseleto]

This should be more efficient that the aggregate arrays, but it relies on 2 strict conditions.

Condition 1:- All identical delivery #'s in the ZSD_ORDERS sheet are in consecutive rows.
Condition 2:- No item will ever be recorded more than once against a single delivery #.

In I3

Please Login or Register  to view this content.

In H3

Please Login or Register  to view this content.

It works! They will always follow the two conditions you outlined above and after trying it on the first 1,300 rows, everything works great! Thanks a lot.

[jason.b75]

You're welcome, thanks for the feedback and the rep

For future reference, the moderators don't like it when you quote big chunks of posts, it's preferred if you just quote one or two lines if you need to highlight a specific point from an earlier reply.
If you just need to direct your reply to a specific participant when serveral people have replied, then you only really need to add a salutation to the start of your reply.