xinyan1129
New Member
- Joined
- Feb 22, 2017
- Messages
- 11
Hi there,
I would like to create a formula in column I to calculate which branch can transfer out stocks to those fast moving branches within same country.
Refer to below table:
Branch code 2002 (in Malaysia), my stock on hand is 1 pc, sales per month is 6 pcs, I have to transfer in about 5 pcs, how can I create a formula in column I to get few matching branches within same country which can transfer according to my "Transfer In Qty". The result must be deducted and cannot be repeated if the quantity already fully transferred out.
I would like to create a formula in column I to calculate which branch can transfer out stocks to those fast moving branches within same country.
Refer to below table:
Branch code 2002 (in Malaysia), my stock on hand is 1 pc, sales per month is 6 pcs, I have to transfer in about 5 pcs, how can I create a formula in column I to get few matching branches within same country which can transfer according to my "Transfer In Qty". The result must be deducted and cannot be repeated if the quantity already fully transferred out.
| A | B | C | D | E | F | G | H | I |
| Branch Code | Product Code | Product Name | Stock on Hand | Sales per month | Country | Transfer In Qty | Transfer Out Qty | Transfer from Country Code |
| 2001 | A2 | A2 Food | 5 | 1 | Malaysia | 4 | ||
| 2002 | A2 | A2 Food | 1 | 6 | Malaysia | 5 | 2001-4 pcs, 2003-1 pcs | |
| 2003 | A2 | A2 Food | 3 | 0 | Malaysia | 3 | ||
| 2007 | A2 | A2 Food | 15 | 8 | Taiwan | 7 | ||
| 2008 | A2 | A2 Food | 2 | 0 | HK | 2 | ||
| 2010 | A2 | A2 Food | 2 | 6 | Malaysia | 4 | 2003-2 pcs, 2015-2 pcs | |
| 2015 | A2 | A2 Food | 5 | 1 | Malaysia | 4 |
