Hello, I am using excel 2013. On Sheet 1 I have a series of columns which contain the following headers (product number, product description, number of cases, and case variant (cases or pieces)). These would be used to reference sheet 2 in order to find the correct line items to pull. Sheet 2 has columns with the headers (storage location, number of cases, product, product description). There are multiple line items containing different quantities of the products from sheet 1 since they are in many different storage locations. Is it possible to reference the quantities on sheet one and find the best storage locations to meet the quantities by referencing sheet 2? For example, if sheet one says product 202451002 (quantity needed 300 cases) I would like it to look at all of the lines on sheet 2 containing that product and return the best storage bins to pull the item from to match the quantity needed (then tell you if there is residual quantity) on sheet 3. Sheet 2 relevant info: Location Product number #Cases location 1 202451002 30 cases location 2 202451002 50 cases location 3 202451002 100 cases location 4 202451002 100 cases location 5 202451002 80 cases In this example it would say on sheet 3, the product number next to the locations you need to pull which would be 1, 3, 4, and 5 and you would have a residual quantity of 10 cases Sheet 3: Product Location #Cases 202451002 location 1 30 cases 202451002 location 3 100 cases 202451002 location 4 100 cases 202451002 location 5 80 cases residual (10 cases) Next item next item next item etc. Is something like this feasible? It would get much more complex as there are usually about 50 items and 1000 different storage locations to look through. Ideally it would give a clean list on sheet 3 of exactly which storage bins to use. Any help would be greatly appreciated. Thank you!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
