Value Allocation/ Distribution across multiple variables

[andrubin]

Hello,

New to the forums, but I couldn't find a solution anywhere so I was hoping someone would see this and may know a trick.

I have an issue where I need to assign "lots" to different orders (varying in amounts) to fulfill an overall order balance. For example:

Overall Order quantity= 3000
Lot A = 320 units
Lot B = 220 units
etc..

Then there are different orders that will pull from these lot amounts that will fulfill the overall balance:

Order1= 20
Order2= 15
etc..

There can be orders that are drawing from multiple lots (since the lot amounts and order amounts will not always line up to meet the overall balance) , but this is something I would like to minimize.

Example: In order to fulfill the overall balance, Order 1 (from above) may need to have 10 of Lot A and 10 of Lot B

What I would like to see in a table is:

Column Header = Lot Name
the Order names that fulfill the balance of the lot
and the overall totals.

Example:

<table width="256" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" width="64" height="20">Lot A</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Total</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Lot B</td> <td class="xl65" style="border-left:none;width:48pt" width="64">Total</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Order1</td> <td class="xl65" style="border-top:none;border-left:none">qty</td> <td class="xl65" style="border-top:none;border-left:none">Order4</td> <td class="xl65" style="border-top:none;border-left:none">qty</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Order2</td> <td class="xl65" style="border-top:none;border-left:none">qty</td> <td class="xl65" style="border-top:none;border-left:none">Order5</td> <td class="xl65" style="border-top:none;border-left:none">qty</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">Order3</td> <td class="xl65" style="border-top:none;border-left:none">qty</td> <td class="xl65" style="border-top:none;border-left:none">Order6</td> <td class="xl65" style="border-top:none;border-left:none">qty</td> </tr> </tbody></table>
I need to be able to use this for different amounts of lots and order amounts.

If you have any formulas or ideas on how to accomplish this, you would help me tremendously.

Thanks!

 

[konew1]

It would help if you posted a section of the data that the analysis is to be drawn from. I think a pivot table might be the answer, but very difficult to be sure from your posting.

 

[andrubin]

The lot amounts and overall order quantity can be arranged any way they need to be, but the majority of the raw data will be:

ColumnA Column B
order1 25
order2 20

etc..

 

[konew1]

How do we know that order 1 is part of lot A?

 

[andrubin]

We don't know that yet. This is what I am trying to accomplish. I want excel to assign the orders to a lot.

So for example, I have order1 = 20... I want excel to assign order1 to a Lot ... let's say excel chooses Lot A (=100), now order1 will be drawing 20 from Lot A (now =80). Once Lot A is finished, excel will not draw from it anymore.

The key to this is optimization though. I want to try and avoid using two or more lots for one order.

Like order1= 15 from LotA, 2 from Lot B, and 3 from LotC = 20 units

Just a note... Lot A + Lot B + Lot C etc.. = overall orders total order1+order2 + order3 etc...

I am trying to be as clear as possible, since this is a very theoretical problem. The data can be arranged however needed.

Thanks again for your help.

 

[konew1]

Any formula solution will start with order 1 an assign it to lots, or start with lot A and split it into orders. This will not achieve your objective of trying to avoid using 2 or more lots for one order. I don't think you can do it by formula. VB code might work, but I don't write code.

 

[andrubin]

Thanks for attempting it anyway, but I need some sort of coding for this or even an excel add-in possibly. I was seeing if anyone knew solutions for this.