SOLVED

To allocate stock from closing stock by formula

Brass Contributor

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?

8 Replies

@Ravindu94 

I am working on this. It may take some time. I'll keep you posted.

@Celia_Alves 

 

ok thanks, im waiting

best response confirmed by Ravindu94 (Brass Contributor)
Solution

@Ravindu94 

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 

 

hey man. its working on proper way. thanx. appreciate your support

You're welcome, @Ravindu94!

I am glad that I could help.

If you could mark my answer as the best answer, it would be great.

Thanks!

@Celia_Alves

 

Done!

 

there is a one more small modification. i have add another chart on sheet 2 and get final RM update on it. i need to make a hyperlink for that cells to filter date on sheet one table when click on sheet 2 table to get which components are not available ( when click sheet 2 table Packing RM column cells to filter data on sheet 1 table based on sub si number)

@Celia_Alves

 

Done!

 

there is a one more small modification. i have add another chart on sheet 2 and get final RM update on it. i need to make a hyperlink for that cells to filter date on sheet one table when click on sheet 2 table to get which components are not available ( when click sheet 2 table Packing RM column cells to filter data on sheet 1 table based on sub si number)

@Ravindu94 

I would need to understand better what you need but I believe you might be able to achieve it using slicers connected to the two tables.

1 best response

Accepted Solutions
best response confirmed by Ravindu94 (Brass Contributor)
Solution

@Ravindu94 

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!

 

View solution in original post