How to review raw data and add lines of the data set together to return specific information

[fmoaveni]

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!

 

[fmoaveni]

Hi,
Sorry, I am not sure how I created multiple threads of the same exact topic. I would still appreciate the help! Thank you!