David Asks: About Primary Keys

Intermediate

About this Tutorial

Create relational databases with index/match. Discover the secret to efficient data management with primary keys! Learn how to sort, filter, and create tables based on unique identifiers in this expert video. Say goodbye to complicated spreadsheets and hello to easy data management.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

INDEX() MATCH()

Video Transcript

<div> Hello. In this video we're gonna answer a member's question. David. He asked about primary keys and what he's explaining is sort of, he has a database of clients or customers, and he has everything in one sheet. He could have, you know, up to like 40, 50 columns of data here. , it gets very difficult to manage multiple columns in the dozens, if not hundreds of columns of data . When you really want to be able to say, take a column out, sort it. And you can do this right with filter , right? You can use the filter , create a filter , you.  have everything here.

But I personally have many problems with the filter function here. Sometimes you don't take everything, sometimes you only have like a filter on some part of it, and then you try to edit it and it discombobulates and it makes things like you can sort here, see, I've sorted that, but because I didn't select this filter , The G column, it didn't get sorted. And so now this looks pretty bad. I know you see all the green around it, but this is not necessarily the best way to do things with this filter function .

So you might want to create another sheet and maybe their name you use as like the primary key. So there's a concept of primary key in something called a relational database where you have a unique number or. Sort of key code, like a unique code that is always going to be this customer one. And now even if you change their name to Andrew, this unique primary key is going to stay the same. Now, in most relational databases, this is actually an iterating key and it starts at one. And for every customer they will have a unique number . It, and it'll always be the next one up.

Now, this is great for databases because your primary key you can actually use your primary key to create other tables. And it's always going to be iterating. So even customer five here and even if I add a new customer, I'll always have a.  number here. And even if I put in Andrew K, right? This is different than this Andrew. And there might be, you know, other, other information here, right? And this is all unique , even though the names might be the same, right? But your primary key is going to be unique no matter. You cannot change it.

If you add a new customer, you add a new primary key, but iterating as well doesn't necessarily help. It, it is good for like MySQL databases, so you can just always see how many customers have you always had, you have total. There are other unique identifiers. One of them is U U I D. You can. , check that out in another video. UU ID is sort of this very long structured number . You can also create random characters. You can base it off their name. There's some other options. But let's talk about sorting, filtering and creating tables based on primary keys.

No matter what your primary key is as long as it's unique , you can do what I'm about to show you. So the main way that people use spreadsheets, right, is they create a main database and. Then have a sort of a sorts you can create, you can sort of copy paste names, maybe that's all the names and you wanna do something with it. But after, let's say you don't want to be updating this, so you do something like array formula . .

I mean, this is pretty gnarly,  way to do it, but what I'm about to show you later and, and how to solve some of these things is also a pretty crazy thing to do. So stay tuned. So here's all of our data from our name column, and we want to say, assign a new thing to it. Say you want to put in a number . And we have some that are different. Some, and how do we get it back to this, right? If we use this array formula and we sort our one, let's sort this A to Z . You see now we don't have everything, right? This name showed up, Andrew's out of order.

These customers are out of order. Or the, they're still in order, but this is all messed up, right? If we sort this, let's say sort this one and go back, see, our names are all out of order and these are not in the correct place. So using this array formula is not a. Way to do this. So let's do it a different way. So what we can do is let's delete all of these, and instead of names, let's always grab the primary key, and that will be, And the thing with primary key is we don't need this array formula , right?

If we have these numbers, we can just put in these numbers as we need to. So we don't need this array formula , meaning it'll always get resorted in everything. We don't need it. We just need to grab primary keys. And now we, how do we know who is who? Well, we can do our new function . So this is added. And what if we wanna actually know what, what is this information? We can do something called well, there's a few ways to this. We can do v lookup, but I always like doing index match . And Index match allows us to grab this data no matter where it is.

What do we want? We want the name first. Let's actually get just a little bit of more information, like three columns. What we wanna do is we wanna match .  with the primary key. We always wanna do search type zero. Now we get column B through D for this primary key. Now this primary key, no matter what it is, let's say we add a new one, eight, we can always add the primary keys. , we, we know what the primary keys are. Those never change. And our index match can copy paste this all the way down. We can copy pastes all the way down.

We're gonna get some errors, but we're gonna get all of our information. So now we can add some more information here. Added data . Okay, add 99 to here and now we can create a added data and do the exact same thing. Index . We do index , and in this case we want to do names and we know we will on the second column, so B through B, and we wanna match our search. Key is going to be our primary key and our range is going to be names A through eight search type 0 0 0. And now we get our inform. We can also auto fill here, and that's just index match .

So index match allows us to create these sort of secondary tables, add data based on the primary key, whatever that primary key is, and get it into a main table , right? This happens to allow us to reconsider how we do sheets and how we combine sheets or create sheets. We first, in fact, Have much less information originally than we needed, right? We don't need 40 columns of data because we can always combine that stuff later.

What we can do is create a relational database based on this primary key, all over a many different sheets, and we can combine those sheets into one main sheet if we do need all the information together in one place. But this is what I like to do. I like to create sort of a relational database, and I use index match to.  and I hope this video helped. There is also the possibility of using V lookup if you do, but always be weary. That V lookup always needs the unique identifier to be in the far left column.

The first column index match does not need that, so actually index match allows a lot more flexibility in getting information and data based in o in other sheets. All right. Hopefully this video was helpful to you.</div>