I currently have a INDEX+MATCH solution in place that I use to pull out matching criterias.. here is the current setup that I work with :-
Have an excel with two sheets, - Data sheet contains an inventory master of sorts where.. we can see each item being displayed with multiple batches in with each of their own quantities depending on where they're stored.. - Sheet 1 is an order form in which my end user would like to get the exact batch of a product on the basis of two criterias.. Criterias being - product number and qty match to fullfill..
Formula being used - =INDEX(C:R,MATCH(1,(C:C=W3)*(R:R>=Y3)*(D:D>=X3),0),3)
Data - Current Inventory
Item Quantity Batch
ABD 10 11223a
ABD 15 24589r
DFG 5 T45678
DFG 67 ghytu8
FGH 10 thnh67
FGH 10 huip78
Sheet 1 - Order form
Item Quantity Batch
ABD 8
DFG 4
DFG 10
FGH 10
New requirement:-
As seen above we have multiple batches for a certain product, now currently if the order quantity is higher than whats available within a single stock module.. The report does not pickup the combination of batches to fulfill the request. Could you please advise on what sort of a function I could use in excel where if the order quantity is higher than that of a single inventory module. It should allocate stocks from that module till its limit and then find the next available module. Please see below example of how the new output should look.
Data - Current Inventory
Item Quantity Batch
ABD 10 11223a
ABD 15 24589r
DFG 5 T45678
DFG 67 ghytu8
FGH 10 thnh67
FGH 10 huip78
Sheet 1 - Order form
Item Quantity Batch Quantity picked from Batch
ABD 18 11223a 10
24589r 8
DFG 6 T45678 5
ghytu8 1
FGH 15 thnh67 10
huip78 5
Can this be resolved with help of any sort advanced excel function/query ?
Thanks.
