We have a list of names and maybe we have sort of a lead categories. Maybe we're doing sort of reach out and we want to actually maybe combine like key words.
Explore ↓
Hey there stranger!
Create a Many to Many Database
About this Tutorial
Sheet Resources
Featured Formulas
Video Transcript
0:00 Hello. So we're gonna create a many to many database here. So in, there's a few use cases for this that I can think of off the top of my head.
0:09 We have a list of names and maybe we have sort of a lead categories. Maybe we're doing sort of reach out and we want to actually maybe combine like key words.
0:19 Like we have, you know, scent and we have follow up, right? Followed up, or we have lost leads and we have, you know, one, we, one leads but sometimes we have something like key words, and we might have, like, this person might be a CEO and they might also have a com, an employee, you know, five employees.
0:47 And you might end up having say, column after column of like maybe some bulls, some like yes or no questions, or you fill out sort of multiple things.
0:58 But really what we want to do, maybe what you're trying to do is put all of that information into one cell and show it right next to this person, right?
1:09 In the ca in the case of like, lead there's only one, but in the case of key words, there might be many, maybe there's like different types of companies that you're reaching out to are different types of people and, and people have more than one category or, or keyword that they're a part of, right?
1:30 People can be both a mother and a ceo. People can be both a father and a cmo, right? <laugh> or a lead software engineer.
1:39 And also be other things, right? <laugh>. So what ends up happening is, right, we, one of the problems you're gonna be facing is you'll probably have multiple categor multiple columns or you're trying to fit in everything into one cell and you're doing it manually, something like this, right?
2:01 C e o and also some other things, right? So you'll know the key, you'll know the problem you're facing when you're adding comma separated values into one cell and you're like, there's gotta be a better way than typing these in or select.
2:17 There's gotta be a better way. There is. The thing is you gotta do a couple things to your sheet, okay?
2:22 So first off, let's, we're gonna create a duplicate of this, and instead of names we're gonna do key words in, so one thing you might be frustrated with is that like, it, it can't be related right away to everything.
2:36 Oh, you might have a list of investors and companies, maybe you have multiple contacts, like contacts, but for this we'll do keywords.
2:48 So what we're gonna do is we're gonna take out everything, every column except for one. And these are just gonna be keywords.
2:58 These are all the keywords we could possibly have and we can always add to them later. I'll show you that.
3:03 I mean, you can just, I don't need to show you that. So we have like ceo, cmo, which are, you know, two different people, but maybe we have five employees, 10 employees no employees.
3:21 We have different things that statuses that can be combined right? Now. Here's the what's gonna happen and here's where the magic happens.
3:29 We're gonna create one more sheet. We're gonna duplicate it, and we're just gonna call this, let's say dv. We need in this two columns, and we need no data.
3:42 You can delete everything in the db. And what we need to do on the left side is we're going to create a dropdown chip.
3:50 Let me move my face. We're gonna create a dropdown from a range, and that range is gonna be names and it's gonna be a, probably a column.
4:03 We're going to go hit done. We're going to also copy this and paste it all the way down. So we have dropdowns everywhere here on the B column and second column, we're gonna do almost the same thing.
4:14 We're gonna insert a dropdown, but in this case, dropdown from range, we're gonna do keywords, a colon, a done, and again, we're gonna copy that one, drop down all the way down to the bottom.
4:37 There we go. I think it was actually was in there. There we go. So now we have two sets of dropdowns from ranges.
4:43 One on the left side, which is our names. We can select someone like Mrs. McKenzie here. And we have keywords like cmo ceo, and five employees here.
4:53 So what we need to do to combine these two groups is for every one we want to add whatever relation we want to add, we just put them next to each other.
5:02 So Mrs. Mackenzie Dietrich is both the CEO of a company with five employees, maybe Fab Wolf is a CEO of 10 employees, and we have Fab Wolf, and we put her all both next to each other, right?
5:18 Order here doesn't necessarily ma necessarily matter, it just matters that you have the relation across a row. So this Fola Wolf is both 10 employees and also ceo.
5:29 You can have these, you can break them up. It can be down here if you forgot to add her, you know, if we have Different person, we can always add Fabiola again at the bottom.
5:43 They don't necessarily be ne need to be next to each other. All right? Now we have to actually perform a little bit of magic here on our names.
5:52 We, the goal of this is that we want all of the keywords in one cell to be displayed here. So how do we do that?
5:59 Well, you might be thinking right away, and you might have already done something like this, but you might do filter.
6:06 And what we need to do is filter db B column B, the second column in the DB column in the DB page.
6:17 And we need db a colon A to equal the name of the person in this row. Okay? So if you do this, let's do this and show what happens.
6:29 We get two things and they, and it goes across multiple cells. We don't want that. We want them all in one cell.
6:35 Well, what you might have already been doing is something like transpose. And what this does is it just takes it vertically and makes it horizontal.
6:43 And there you again, problematic because our keywords are across many cells, and maybe there's other data we have here. We want everything in one cell.
6:53 So we need to do adjoin. And what we do is we join, we can join any del denominator, we'll do a comma here, for instance.
7:01 And all of our values are gonna be the filter that we searched for, okay? And now in one cell, all of our keywords, no matter how many we have, are joined here together.
7:14 So our DB over here, we can add Mrs. McKenzie Dietrich, we can add maybe she's also CMO for some reason.
7:24 And there it goes. It gets added automatically to the keyword column here on our front page. And same filter can be copied down.
7:36 And we have the filter for Carola. And one thing you're gonna get is probably na we can fix that by wrapping this with, I think we can wrap.
7:50 Let's try wrapping this f n a nothing. Let's see if that works. And there we go. We got nothing. If we have nothing, no filter.
8:02 The, the NA is saying no, no matche is found. You can also have some text there as like no keywords found, or add some keywords, no key words.
8:14 So you can have some text there, but you also might just want nothing. You want, might want to blank so that visually you can see, oh, there's nothing here.
8:22 I need to add this. Or you can have a command that says add keywords. There you go. And we can add keywords as we want.
8:29 And now this entire database that the DB is completely manual, we can add information here as we want. And this keyword column is automatically added and it's all in one cell.
8:41 It's all in one column. So no matter how many we have there keywords, they'll all be added and they'll be joined by a comma.
8:48 We can also add some other joins here. Any other delimiter, like a pipe or maybe you want some spaces, maybe not.
8:55 Spaces, spaces are pretty bad here. Things we're watching, and I hope this helps you create a many to many database in Google Sheets.
9:03 Bye.
0:09 We have a list of names and maybe we have sort of a lead categories. Maybe we're doing sort of reach out and we want to actually maybe combine like key words.
0:19 Like we have, you know, scent and we have follow up, right? Followed up, or we have lost leads and we have, you know, one, we, one leads but sometimes we have something like key words, and we might have, like, this person might be a CEO and they might also have a com, an employee, you know, five employees.
0:47 And you might end up having say, column after column of like maybe some bulls, some like yes or no questions, or you fill out sort of multiple things.
0:58 But really what we want to do, maybe what you're trying to do is put all of that information into one cell and show it right next to this person, right?
1:09 In the ca in the case of like, lead there's only one, but in the case of key words, there might be many, maybe there's like different types of companies that you're reaching out to are different types of people and, and people have more than one category or, or keyword that they're a part of, right?
1:30 People can be both a mother and a ceo. People can be both a father and a cmo, right? <laugh> or a lead software engineer.
1:39 And also be other things, right? <laugh>. So what ends up happening is, right, we, one of the problems you're gonna be facing is you'll probably have multiple categor multiple columns or you're trying to fit in everything into one cell and you're doing it manually, something like this, right?
2:01 C e o and also some other things, right? So you'll know the key, you'll know the problem you're facing when you're adding comma separated values into one cell and you're like, there's gotta be a better way than typing these in or select.
2:17 There's gotta be a better way. There is. The thing is you gotta do a couple things to your sheet, okay?
2:22 So first off, let's, we're gonna create a duplicate of this, and instead of names we're gonna do key words in, so one thing you might be frustrated with is that like, it, it can't be related right away to everything.
2:36 Oh, you might have a list of investors and companies, maybe you have multiple contacts, like contacts, but for this we'll do keywords.
2:48 So what we're gonna do is we're gonna take out everything, every column except for one. And these are just gonna be keywords.
2:58 These are all the keywords we could possibly have and we can always add to them later. I'll show you that.
3:03 I mean, you can just, I don't need to show you that. So we have like ceo, cmo, which are, you know, two different people, but maybe we have five employees, 10 employees no employees.
3:21 We have different things that statuses that can be combined right? Now. Here's the what's gonna happen and here's where the magic happens.
3:29 We're gonna create one more sheet. We're gonna duplicate it, and we're just gonna call this, let's say dv. We need in this two columns, and we need no data.
3:42 You can delete everything in the db. And what we need to do on the left side is we're going to create a dropdown chip.
3:50 Let me move my face. We're gonna create a dropdown from a range, and that range is gonna be names and it's gonna be a, probably a column.
4:03 We're going to go hit done. We're going to also copy this and paste it all the way down. So we have dropdowns everywhere here on the B column and second column, we're gonna do almost the same thing.
4:14 We're gonna insert a dropdown, but in this case, dropdown from range, we're gonna do keywords, a colon, a done, and again, we're gonna copy that one, drop down all the way down to the bottom.
4:37 There we go. I think it was actually was in there. There we go. So now we have two sets of dropdowns from ranges.
4:43 One on the left side, which is our names. We can select someone like Mrs. McKenzie here. And we have keywords like cmo ceo, and five employees here.
4:53 So what we need to do to combine these two groups is for every one we want to add whatever relation we want to add, we just put them next to each other.
5:02 So Mrs. Mackenzie Dietrich is both the CEO of a company with five employees, maybe Fab Wolf is a CEO of 10 employees, and we have Fab Wolf, and we put her all both next to each other, right?
5:18 Order here doesn't necessarily ma necessarily matter, it just matters that you have the relation across a row. So this Fola Wolf is both 10 employees and also ceo.
5:29 You can have these, you can break them up. It can be down here if you forgot to add her, you know, if we have Different person, we can always add Fabiola again at the bottom.
5:43 They don't necessarily be ne need to be next to each other. All right? Now we have to actually perform a little bit of magic here on our names.
5:52 We, the goal of this is that we want all of the keywords in one cell to be displayed here. So how do we do that?
5:59 Well, you might be thinking right away, and you might have already done something like this, but you might do filter.
6:06 And what we need to do is filter db B column B, the second column in the DB column in the DB page.
6:17 And we need db a colon A to equal the name of the person in this row. Okay? So if you do this, let's do this and show what happens.
6:29 We get two things and they, and it goes across multiple cells. We don't want that. We want them all in one cell.
6:35 Well, what you might have already been doing is something like transpose. And what this does is it just takes it vertically and makes it horizontal.
6:43 And there you again, problematic because our keywords are across many cells, and maybe there's other data we have here. We want everything in one cell.
6:53 So we need to do adjoin. And what we do is we join, we can join any del denominator, we'll do a comma here, for instance.
7:01 And all of our values are gonna be the filter that we searched for, okay? And now in one cell, all of our keywords, no matter how many we have, are joined here together.
7:14 So our DB over here, we can add Mrs. McKenzie Dietrich, we can add maybe she's also CMO for some reason.
7:24 And there it goes. It gets added automatically to the keyword column here on our front page. And same filter can be copied down.
7:36 And we have the filter for Carola. And one thing you're gonna get is probably na we can fix that by wrapping this with, I think we can wrap.
7:50 Let's try wrapping this f n a nothing. Let's see if that works. And there we go. We got nothing. If we have nothing, no filter.
8:02 The, the NA is saying no, no matche is found. You can also have some text there as like no keywords found, or add some keywords, no key words.
8:14 So you can have some text there, but you also might just want nothing. You want, might want to blank so that visually you can see, oh, there's nothing here.
8:22 I need to add this. Or you can have a command that says add keywords. There you go. And we can add keywords as we want.
8:29 And now this entire database that the DB is completely manual, we can add information here as we want. And this keyword column is automatically added and it's all in one cell.
8:41 It's all in one column. So no matter how many we have there keywords, they'll all be added and they'll be joined by a comma.
8:48 We can also add some other joins here. Any other delimiter, like a pipe or maybe you want some spaces, maybe not.
8:55 Spaces, spaces are pretty bad here. Things we're watching, and I hope this helps you create a many to many database in Google Sheets.
9:03 Bye.