I initially tried to do this directly in SQL Server but it seems like it can't be possible through query so I want to calculate this "Distribute" column in Excel. Below is the details of the question. Appreciate if someone can help here.
I have following column in Excel and want to calculate values in "Distribute" column.
| Item | Qty | Customer | Rank | Min | Max | Distribute |
|---|---|---|---|---|---|---|
| 001 | 1500 | 0101 | 1 | 250 | 600 | ???? |
| 001 | 1500 | 0104 | 2 | 0 | 500 | ???? |
| 001 | 1500 | 0103 | 3 | 100 | 300 | ???? |
| 001 | 1500 | 0105 | 4 | 200 | 300 | ???? |
| 002 | 2000 | 0104 | 1 | 200 | 600 | ???? |
| 002 | 2000 | 0105 | 2 | 150 | 700 | ???? |
| 002 | 2000 | 0101 | 3 | 100 | 200 | ???? |
| 002 | 2000 | 0103 | 4 | 100 | 500 | ???? |
| 002 | 2000 | 0102 | 5 | 50 | 200 | ???? |
| 003 | 800 | 0103 | 1 | 100 | 500 | ???? |
| 003 | 800 | 0102 | 2 | 50 | 200 | ???? |
| 003 | 800 | 0101 | 2 | 50 | 100 | ???? |
| 003 | 800 | 0104 | 3 | 50 | 80 | ???? |
There are multiple items (Item) and each item has fixed quantity available (Qty)
Each item is distributed in different customers (Customers) based on their rank (Rank). The ranks are group by for every item. Data is already sorted via Rank column for every item. Multiple customers against an item can have same rank.
From the total quantity (Qty) of each item, every customer must get minimum quantity mentioned in (Min) column irrespective of its rank.
The remaining quantity of every item must be distribute based on the rank of the customer making sure that it should not exceed to the maximum quantity mentioned in (Max) column.
It is OK, if total quantity of the item is not consumed after distribution maximum quantity to all customer.
What I am after is the result something like this:
| Item | Qty | Customer | Rank | Min | Max | Distribute |
|---|---|---|---|---|---|---|
| 001 | 1500 | 0101 | 1 | 250 | 600 | 600 |
| 001 | 1500 | 0104 | 2 | 0 | 500 | 500 |
| 001 | 1500 | 0103 | 3 | 100 | 300 | 200 |
| 001 | 1500 | 0105 | 4 | 200 | 300 | 200 |
| 002 | 2000 | 0104 | 1 | 200 | 600 | 600 |
| 002 | 2000 | 0105 | 2 | 150 | 700 | 700 |
| 002 | 2000 | 0101 | 3 | 100 | 200 | 200 |
| 002 | 2000 | 0103 | 4 | 100 | 500 | 450 |
| 002 | 2000 | 0102 | 5 | 50 | 200 | 50 |
| 003 | 800 | 0103 | 1 | 100 | 500 | 500 |
| 003 | 800 | 0102 | 2 | 50 | 200 | 200 |
| 003 | 800 | 0101 | 2 | 50 | 100 | 50 |
| 003 | 800 | 0104 | 3 | 50 | 80 | 50 |
Looking forward if you can provide a formula or solution here. Thanks for your help.
