You have an inventory pick list and master inventory list. But, the pick list only has codes. You need to match the codes in the pick list with the names of the items in the master list. Only then do you know what to get.
- [Instructor] We work in a warehouse and we get these Pick Lists that give us a product I.D., and a quantity. We don't know what these things are. So we've gotta use a Master List on our own to pull the detail over, and the in-stock number, so that we know what we're lookin' for. And, for DC7724, whatever that is, we're supposed to get 14. What if we've only got eight and we've gone all the way to the other side of the warehouse just to find that out? So here is where your help is needed.
Take the data in the Pick List. Refer to the Master List and bring over what the detail is and the number in stock. Then, create a summary to let us know if we've got enough of what's needed. Pause the video, fill in this Pick List, come back, and I'll show you a solution. Now there are a lot of ways to solve this, and I'm gonna use Power Query to do this.
I'm gonna click inside this data set, go to Data, From Table. Table does have headers. And I'm gonna name this Pick List. And I'm gonna close and load this. Back to sheet one. Same thing with the Master List. Data, From Table.
Table has headers. I'm gonna call this query M List. Close and load it. Now I'm gonna do a merge, or a join, of these two lists. Go to Data. New Query, Combine Queries. Merge. Up top, I want my Pick List. And on the bottom, the Master List. And I want to connect them via the product I.D.
or item I.D. And I'm gonna do a Left Outer Join. Okay. Now our Pick List is showing and the Master List is collapsed, so I'm gonna Expand. Which columns do I want? I want the Detail and the In Stock. So I'm gonna unselect 'em all, get the Detail, In Stock. Unselect that. Okay. Now I don't need this In Stock column.
Right click, remove. I don't need this detail. Right click, remove. And then close and load. Now I need a column letting us know if we have enough of what's needed. So I'm gonna go here. We can call this anything. I'm gonna call this Enough. Enter. And now let's compare. Equals, we wanna know, based on In Stock, do we have enough of what's needed based on the quantity in column B.
We wanna select this eight here, minus, and then select this eight here. Enter. Now we can see in row 10 we are five short of eight ounce Lemon Yellow.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.