May 01 2019 10:16 PM
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?
May 02 2019 07:30 AM
I am working on this. It may take some time. I'll keep you posted.
May 02 2019 08:56 PM
May 03 2019 05:16 AM
SolutionPlease 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!
May 05 2019 08:45 PM
May 06 2019 07:39 AM
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!
May 06 2019 09:11 PM
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)
May 06 2019 09:11 PM
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)
May 07 2019 03:10 AM
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.
May 03 2019 05:16 AM
SolutionPlease 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!