Forum Discussion
To allocate stock from closing stock by formula
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!
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!
Celia_Alves Suppose I have multiple items in multiple Orders, now I want to allocate not by items, instead I shall allocate order number wise & system will check automatically in which order number 100% items are covered up as per my stock, that will be allocated first, thereafter 99% after first allocation, then 98%, in this way gradually system will decrease the percentage & lastly system will allocate as per stock availability. In such case how can it be done using formula ?
