Forum Discussion
To allocate stock from closing stock by formula
HI Guys,
i've tried lots of times to get a solution for my problem. but still not sorted that. here attached my chart with two tables. in first table i have mentioned order number delivery date of that order number / required items order number wise and required qtys. in my second table i have mentioned items wise closing stock details. most of the items common for every order number. i need to allocate stock to first table from second table closing stock. for this i need to prioritize delivery date. as a example if i have 8000 pcs of closing stock in one item in second table and 1st table 1 have 5000 wise two deliveries. i need to allocate 1st 5000 for first delivery and balance 3000 for second delivery) any one can help for me?
Please see attached.
I converted your data ranges to actual excel tables.
The columns in grey have formulas. Do not overwrite them.The values in the date column are not being recognized as dates.
I created a column "Converted Dates" that transforms your values into a date. Please continue to use your column to delivery dates. The other one will be used in the calculations and you can hide it if you want.Same Product Order Rank - determines, for the same product, which is due first based on the date and then based on the quantity. For two orders of the same product with the same delivery date, the smaller quantities get filled first. If there are two or more orders of the same product with the same delivery date and same quantity, then order number sets the final priority for the rank calculation.
The rank numbers can start, for the same product, at different values, depending on the number of existing orders for that same product. What matters is that for the same product the calculated values rank the orders correctly.You can sort your table as you wish, using the sorting buttons at the top of the columns.
At the moment, the Table is sorted by
Product name alphabetically
then by converted date
then by qty
so that you can see the ranks and the quantity allocation working.(Google how to Custom sort a table in Excel with several criteria, if you don't know how to do this.)I hope this helps. Have a great day!
10 Replies
I am working on this. It may take some time. I'll keep you posted.
Please see attached.
I converted your data ranges to actual excel tables.
The columns in grey have formulas. Do not overwrite them.The values in the date column are not being recognized as dates.
I created a column "Converted Dates" that transforms your values into a date. Please continue to use your column to delivery dates. The other one will be used in the calculations and you can hide it if you want.Same Product Order Rank - determines, for the same product, which is due first based on the date and then based on the quantity. For two orders of the same product with the same delivery date, the smaller quantities get filled first. If there are two or more orders of the same product with the same delivery date and same quantity, then order number sets the final priority for the rank calculation.
The rank numbers can start, for the same product, at different values, depending on the number of existing orders for that same product. What matters is that for the same product the calculated values rank the orders correctly.You can sort your table as you wish, using the sorting buttons at the top of the columns.
At the moment, the Table is sorted by
Product name alphabetically
then by converted date
then by qty
so that you can see the ranks and the quantity allocation working.(Google how to Custom sort a table in Excel with several criteria, if you don't know how to do this.)I hope this helps. Have a great day!
