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

Status
Not open for further replies.

fmoaveni

New Member
Joined
Nov 23, 2015
Messages
38
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Not a very patient person are you?

Well I dunno though.....gave us one entire minute before the second try and then THREE minutes before the third. Gettin better ;)
 
Last edited:
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top