00:00I recently posted a video demonstrating how to craft an inventory management spreadsheet
00:04using only three simple formulas. However, there was considerable amount of interest in developing
00:09inventory management system based on individual item. Well, this is how you do it. So this is my
00:14data set here. I got my main database here itself and what I did was I created another new table
00:21for my nominal inventory level here. So if you do have this table, the next few steps you don't have
00:26to follow. But if you don't, this is what I normally do. I'm going to say equal unique
00:33and then I'm going to basically highlight these fruits here from my original master database table
00:38here and close parentheses and hit enter. And what that does is that it gives me all the unique values
00:44on my fruit here. I like to keep my items sorted so they're easy to find. Basically put the unique
00:49encapsulate unit under the sort function and hit enter and now they're in alphabetical order.
00:55Next, I'll fill up the normal low and order number from some repository like this. And once that's done,
01:01now you have the nominal inventory level here. Let's say for example, Apple, the normal level is 900,
01:07low level is 610, and order now is 370 here. So next thing I do is to find out the level, the text
01:14base level for Apple. Let's say is 525. Apple should be showing at about low level here like this. So the way to
01:24do it is, let me break down the formula to start off with. Let's look at Apple here on this first row
01:28here. You're trying to find the text base level here from this particular header here. So the first thing
01:34I have to do with this Apple is to actually get this row based on Apple itself. For that, you're going to
01:40use a function called filter, open parenthesis. The first argument on that filter will be the complete
01:47list for the inventory itself. You're going to press F4 now, keeping in mind that you're going to apply this
01:52formula for the rest of the rows, comma. Second one will be the condition itself. And I'm going to use
01:58this fruit list as the condition. As you can see, it's showing as pound indicating to follow whatever
02:04the size of the array is, which is size is 28 rows. That's why they put pound here. I'm going to press
02:10F4 to make it fix row column. And then this array has to equal, if it's equal to say, Apple, fruit,
02:18close parenthesis, and hit enter, this will give you the line here, this particular line,
02:23right? That's why it's showing that let's say if you change this to say 920, you can see it updates
02:27there. With that filter formula getting this line here, let's say if you want to get this 370 from this
02:33record here, the formula to use is basically repurpose that filter formula here and encapsulate the filter
02:39formula with the function called index. And the first argument would be the filter function itself.
02:45The second argument would be the column number of that particular row or record. For this one here,
02:52this is number four counting as one, two, three, four. So this column number four, you're going to put
02:58four here, close parenthesis and hit enter, it'll give you 370. Now that we have established how we can
03:03get each of this item on the nominal table, what you can do is you can marry this with if statement here.
03:09Let me explain. You're going to say if as open parenthesis, if this inventory level for apple
03:16is less than or equal to, we're going to start off with order now, this index and filter which is going
03:21to give you 370 like what we're shown just now. If your current inventory is less than or equal to 370,
03:29you're going to display order now. You're going to press F4 once to make the row and column fix.
03:35The second condition would be the same thing as the first one. You're going to say I4 less than or equal
03:42to the index filter. But now we are after the third column here of 610, this one here. So you're going
03:50to just have to change this from 4 to 3, which is going to return 610. If your current inventory is
03:58less than 610 for apple, you're going to display a low text on this particular text-based level here.
04:04You're going to press F4 to make both row and column fix. And the third condition would be
04:10I4, which is inventory, less than or equal to the index filter. But this time around, we are after
04:19column number 2. The first one was 4, this is 3, and this is 2. If this particular inventory is less than
04:28920, we're going to show a normal text like this. Press F4 once to make both row and column fix.
04:36Once it's all done, close parentheses and hit enter. You should see a value of low because this thing is
04:42below 570 but greater than 240. But if you are to amp it up to say maybe 900 like that, you can see it
04:48goes back to normal. So once you establish this formula is working, apply the same formula to the
04:54rest of the rows and each individual text-based inventory is reflected based on its own fruit type
05:00here. Now we can associate a filter for this here so that the user can make selection. You go to data from
05:07the ribbon, go data validation, data validation. In the settings column here, you're going to select
05:14list, or rather the allow, you're going to select list, make sure in setting tab. And this one here,
05:18you're going to select your normal, low, and order now like that. And hit enter twice, one, two,
05:25and now you should have three selection. I'm just going to select low like this. Once the user selects,
05:30you're going to list down all the inventory that pertains, which has a low value. So the formula to use is
05:35equal filter. The first argument of filter will be the complete data set array all the way down here,
05:41comma. And the second argument is the array for the level. And we are looking for if your array of
05:48level is equal to the pull down list that the user have selected. Close parenthesis and hit enter. And
05:55you can see it lists down anything right low. Now if you were to change this into order now, only order now
06:02inventory is displayed. And likewise, you put normal, only normal is displayed.
