IF Statement for multiple lines

[mdeadrick]

Hi guys, first time poster. Have been trying to solve something for a while, and have not found a solution. I am fairly novice with using Excel. Any help would be greatly appreciated!

Essentially, I am trying to create a warehouse assignment sheet, where the assigned warehouse is based off of certain logic:

IF all the line items for the order numbers are at available at the "EB" warehouse, then assign all line items to "EB" in the warehouse column.
IF all the line items are not available at "EB" but are available at "VGM", then assign all line items to "VGM" in the warehouse column.
IF all the line items are not available at "EB" or "VGM", then assign all line items to "PPM" in the warehouse column.

I was able to create a bootleg version of this logic for order numbers with ONE line item, but was not able to figure out the logic to apply this to order numbers with MULTIPLE line items. Is there any way to make this logic work for multiple line item order numbers?

Thank you for any assistance. I have included an attachment as well.

[davesexcel]

I think this is it, you may have to edit it.

Please Login or Register  to view this content.

[mdeadrick]

Hi davesexcel. Thanks for the quick response and willingness to help! One thing that I failed to mention in my original post of the problem: If an order contains multiple line items, we are trying to assign the multiple line items to the same warehouse, as to not split shipments. For example cells A10 to A12 should all return "VGM", as to not split the order between "EB" and "VGM" (see attachment).

Can you think of any formula to incorporate that logic?

[JeteMc]

Try the following:

Please Login or Register  to view this content.

Was not sure how you would want to handle a case where none of the warehouses could handle the entire order, such as order # 1507139674-471 (rows 19:21), so had to guess on that one.
Let us know if you have any questions.

[JeteMc]

This may produce more aesthetically pleasing output:

Please Login or Register  to view this content.

Let us know if you have any questions.

[mdeadrick]

Wow, JeteMc, I cannot thank you enough. This logic will save my company 15 hours a week or more! A quick follow-up question: Do you know if it is possible to display the assigned warehouse to all of the line items for an order number, rather than just the first line of the order? Please see example image attached.

[JeteMc]

You're Welcome and thank you for the feedback. The formula in post #4 shows the assigned warehouse on all lines, the formula in post #5 is just an option. If the solution is satisfactory, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.