Hi,
I have a warehouse master list with several item no. positions. Each item no. occurs more than once, and each occurence has its own bin code, lot no. and quantity. For example, an item no. may be located in several different bins nad have several different lot numbers. A bin may contain several different items, each with its own lot no, as well same two or more item numbers of the same lot no., etc.
In my other tab I have a list of items with required quantity for each item. I managed to build a formula that looks up single items from the master list that match the greater than or equal to the required quantity condition, and returns their bin code and lot no. However, some of the items in the master list do not meet the required quantity condition, and the formula returns an error. I managed to build another formula that adds up all items with the same item no. per (not in) every lot no. per every bin. Other formuls then return the bin and lot no. that contains the largest summed quantity of an item.
What I'm looking for is to lookup a bin and lot no. of the minimal item's qty sum that meets the greater than or equal to required qty condition. If my understanding is right, I need to somehow replace the MAX in my formula with a MIN and IF, but couldn't seem to have any luck with that. Any help would be appreciated.
I have a warehouse master list with several item no. positions. Each item no. occurs more than once, and each occurence has its own bin code, lot no. and quantity. For example, an item no. may be located in several different bins nad have several different lot numbers. A bin may contain several different items, each with its own lot no, as well same two or more item numbers of the same lot no., etc.
In my other tab I have a list of items with required quantity for each item. I managed to build a formula that looks up single items from the master list that match the greater than or equal to the required quantity condition, and returns their bin code and lot no. However, some of the items in the master list do not meet the required quantity condition, and the formula returns an error. I managed to build another formula that adds up all items with the same item no. per (not in) every lot no. per every bin. Other formuls then return the bin and lot no. that contains the largest summed quantity of an item.
What I'm looking for is to lookup a bin and lot no. of the minimal item's qty sum that meets the greater than or equal to required qty condition. If my understanding is right, I need to somehow replace the MAX in my formula with a MIN and IF, but couldn't seem to have any luck with that. Any help would be appreciated.
| project.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Item | Required qty | Matching qty bin | Matching qty lot no. | Mathcing qty | Max sum lot no. | Max sum bin code | ||
| 2 | 101 | 100 | #N/D | #N/D | 0 | 1A | 1/2/2 | ||
| 3 | 102 | 20 | 1/3/2 | 2A | 20 | 2A | 1/2/1 | ||
| 4 | 103 | 125 | #N/D | #N/D | 0 | 3B | 2/2/1 | ||
| 5 | 104 | 35 | 3/2/3 | 4D | 35 | 4A | 3/2/1 | ||
| 6 | 105 | 100 | #N/D | #N/D | 0 | 5A | 2/7/2 | ||
| 7 | 106 | 10 | 4/4/4 | 6B | 15 | 6C | 4/4/6 | ||
PickList | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C7 | C2 | =INDEX(Bins,MATCH(1,([@[Mathcing qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),2) |
| D2:D7 | D2 | =INDEX(Bins,MATCH(1,([@[Mathcing qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),3) |
| E2:E7 | E2 | =MIN(IF([@Item]=Bins[Item],IF([@[Required qty]]<=Bins[Qty],Bins[Qty]))) |
| F2:F7 | F2 | =INDEX(Bins[Lot no.],MATCH(MAX(SUMIF(Bins[Lot no.],IF([@Item]=Bins[Item],Bins[Lot no.]),Bins[Qty])),SUMIF(Bins[Lot no.],IF([@Item]=Bins[Item],Bins[Lot no.]),Bins[Qty]),0)) |
| G2:G7 | G2 | =INDEX(Bins[Bin],MATCH(MAX(SUMIF(Bins[Bin],IF([@Item]=Bins[Item],Bins[Bin]),Bins[Qty])),SUMIF(Bins[Bin],IF([@Item]=Bins[Item],Bins[Bin]),Bins[Qty]),0)) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
Last edited:
