Creating Searchable Databases In Numbers

Hi, this is Gary with MacMost.com. Let me show you how to make searchable data bases in Numbers. 

Let's say you have a relatively simple Numbers table, like this one. This just keeps track of expenses. Everything has got a date, a description, an amount, and a category. Let's say you don't want to view it all at once. You want to easily be able to search this. You, of course, can just use Command F and then search for something. So, for instance, you can search for pub and it will find the word pub here. But what you'd rather do is have it filter this table to just show you things that match a Search. So an easy way to do this, without actually building anything at all, is to use the Filter Function. 

So let's say I just want to see everything here in category that matches transportation. So what I can do is select the Table and then go to the letters at the top for each column. Click on the Context Menu button here and you'll see Show Filter Options and Quick Filter. If I choose Quick Filter it's going to show me everything it finds in this column and how many times it finds it. So, for instance, for, say, transportation there are 28 here. I can easily filter everything by unchecking what I don't want to see. Or I can click Deselect All and then select just transportation. Now you can see I can easily look at everything that has transportation in the category column. But, of course, this is difficult to work with if you're constantly wanting to filter the table. 

So instead let's build something that will allow us to do this easily. Let's start with the Description Column here. Say we want to search for something in the Description Column. We're going to create a new table and I'm going to choose the simplest type of table, one with no Header, Row, or Column right here. I'm going to make it a 1 x 1 table like this. I'm going to call this Description Search. This is where we will type what we want to see. So, for instance, if we are going to look for Charity in the Description, like that, we should be able to type that here and then it will automatically filter this whole table showing us only things that match this. So let's make that happen. To do that we're going to add another column here and we're going to call this one Match Description. Now we're going to put a formula in here that's going to test whether the description here matches what is shown here in this special search field. So I'm going to press the equals key with this cell selected and start typing my formula. We're going to look to see if this cell here contains what's in here. So we're going to look on the right under Functions and we're going to look under Text here and find something that will work for that. 

The Find function seems to do what we want. It just looks for a Search string inside of a Source string. So we'll add Find here. The Search String is this. We'll select it and want to click it there and then set Preserve Row, Preserve Column. So as we Copy & Paste this throughout the table it only refers to this one cell. It doesn't try to move down relative to where we paste it. But the Source String, what we're searching here, that's going to be this one and that is going to move down relative to where we paste it. We don't need to worry about the Start position. That's optional so we will just leave that blank. We'll click the checkbox here. So it couldn't find charity in this one. We'll Copy this cell, double click on the letter here at the top of the column and Paste to paste it in every cell in this column. We'll see it does find one here. It gives a one there as opposed to an error. So now if we filter this table with just the ones that have a 1 in them, I can do that with a quick filter here, then I'll just get the ones that match that. 

Now if I were to change this to, say, restaurant like that, then I'm going to get the ones that just match restaurant. Now since we're looking for something inside we don't have to actually match the entire thing. I can just match, say, the first four letters like that and it works, or that and it works. If it is blank it will always match which is exactly what we want. Now that we have this we can Hide this column here so we don't have to see it. 

If you find these videos valuable consider joining the more than 2000 others that support MacMost at Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

Now let's add a second search field for Category here. So I'm going to add another table here and reduce this to just one cell, like that. We'll call this Category Search, like that. This is where we'll have the category. So, for instance, if I type Food in here it should match everything that has food in Category. So to get that working we can do the same thing here. Let's Unhide that column we had there before and we'll add an additional one and we'll call this Match Category. Then we need to put a formula here. But instead of searching in the String for this we want to match exactly. That, in general, is pretty easy. If this cell equals this cell here, and I want to make sure I Preserve Row, Preserve Column for this. Then that's it. Just check to see if it's equal. I'm going to get either a True or False value with it. So I'll Copy, double click on the letter, and Paste and you can see True and False values depending upon whether this matches this. 

Now we can Filter by that as well. But before we do that let's modify this a bit. Instead of just checking for this I'm going to use Or and that's a function here. The first thing we're going to check is what we were checking before. We're also going to check to see if this and again we'll do Preserve Row, Preserve Column and if it is equal to Blank. So in other words if they match or if there is nothing in here then this will be True. So now I want to Copy & Paste this throughout. You've got to remember to do that to update all the rest of these. So now if this has a value in it, it matches it. But if there is no value in it then it is equal to Blank and they are all True. 

Now I'm going to make sure I have this table selected here and I'm going to go to Organize Filter. I'll see that Quick Filter I added here for the Match description. It's just looking for, if the value is 1. We're going to add another filter here and we're going to add this for Match Category. The rule here, I can do Quick Filter as well, I can also go to Cell and say, Is True. Both will get me the same thing. So now if the filters are the description must match 1 and the Match Category is True. If both of these are True, it matches all filters then we'll see the result there. So I can do a search here for, say, Food and then I can do a Description Search and I can search just for GRO for grocery and now we'll see Food and Grocery, both filtered out. If I clear this out I'll see only grocery which will give me the same result. But if I clear this out here I can see that, say, Searching For the letter G gets me grocery and gas. But searching here for only show me Food now it just gets me grocery with food. I can go and Hide these columns here.

Now you may be asking well I'd really like to have a Pop-up Menu for this rather than having to type. You wouldn't want it for this because we're searching for text inside of text so you want to be able to type something that matches a substring, like maybe Doctor Co-Pay or Doctor Visit by just typing Doctor. But for Category Search we have specific categories that we're following here. So we could select this cell, go to Format, go to Cell and change this to Pop-Up Menu. So when we do we get these items here and we have to actually type in like food and transportation like that and then we can select here, like this. You can see how easy it is now to search for something. If we had a lot of categories here there's a trick to get them all into this field. Let's go back and change this just to Automatic here so it just text that we type. I'm going to double-click on the letter here to select all of these and change Format, Cell, Data Format to Pop-Up Menu. So now these are all Pop-Up Menus and it populates it with all of the values it finds in all of the selected cells. That's really handy and you may want to leave these as Pop-Up Menus to make it easier to change them or add new ones. But you could also select one here, Copy, go over here and Paste and that will paste in that Pop-Up Menu, like this. You can see I get all of the different options there. Plus, I can change this to start with Blank and now I have an additional item here that's None. That allows me to set this to Blank and see everything in Categories here. You can then decide whether or not to keep these as Pop-Up Menus or double-click to select them all and change it back to Automatic or just Text for these. 

Let's add one more type of search and that's for a Date Range. So I'm going to add a new table here. I'm going to make it a blank table and I'm going to have two cells here. I'm going to call this Date Range. The first one is going to be the Start Date. So let's start with something like 6/1/2024. The end will be say 8/15/2024, just as an example. Now we want to filter out all of these by this Date column according to what fits in-between here. So to do that we want to add yet another column. We'll call this Match Date. Here this form is going to be a bit more complex. I'll do equals here and then I'm going to have two things I need to match. If it's On or After this date and if it is Before or On this date. So I want to do AND for two conditions. The first one is that this date should be greater than or equal to this date. The second one should be that this same date here should be less than or equal to this date. Then I want to make sure, like before, that I Preserve Row, Preserve Column for both of these, like that. Now we'll get a result here that gives us True or False if it matches that. So if I scroll down here I can see a bunch of Trues starting here on the beginning of June. So now I simply want to add another filter here. So I'll select the table here. I'll go to Organize, Filters and I'll add a new filter. This time for Match Date. I'll have it set to if it is True, I'll use cell is True. Now it is only going to match those that are True. I will change this to Hide this column as well. So all three of my Match columns are hidden. Now I can do things like, for instance, Category, Search for Housing. Then only search in the date range between June 1st and August 25th of 2024. You can see that is exactly what I get here. If I were to look in Description here and Search For, say, R you can see it only shows me Rent. So I've got all of these working. These I can make Blank and this one I can set to None and this I can simply just set it to a large date range to show everything. Now you have a pretty comprehensive way to Search this database. You don't have to get as complex as this and use all three of these. You could just use one for a fairly simple search. 

I'll include this example document at the post at MacMost.com for you to examine. Hope you found this useful. Thanks for watching.