Watch this Google Sheets tutorial to find out how to know which new names appear on a new list, that's not on a main list.
Hello, welcome to Better Sheets. The YouTube channel brought to you by BetterSheets.co, today's video is a question from a member. Wallace asked this question about adding names to a list.
They have a main list. They have new names that they don't know if they're on that list and they're hunting and pecking and trying to find out how many people are on this list. I show in a few moments, exactly how to get the list of new members to add to that list.
But also I go deeper into figuring out that consistently, where can I just add a list to one tab and get another list on another tab of people to add? Makes it super simple. Once you go through a few little formulas, we're just using formulas in this sheet. No scripts, no scripting. It's very basic formulas that you can get.
You probably want to get to this video. You're wondering how do I do this? How do I have a list of names? Who's on the list. Who's not on the list.
How do I know? Watch this video.
Welcome. So we have a question from a member Wallace who is asking about finding new names from a list that there is a main list of names. And Wallace wants to know, I have a new list of names and I wanna know which names are on that list and which names are not on that list. So let me go through the question again and then we'll get to the answer.
So. He's asking Wallace is asking, what's the easiest way to take two columns in separate sheets. Find out if any names are missing from a column. He has a master list of names and another list from a recent meeting, trying to find out who's not on the main or master list. The master list has over 700 names.
The meeting list has 175 names on it. Some are on the master, some are not and , the objective is to get the names. that do not show up on the main list so you can add them to the main list and what could happen. Right. If we have a main list here, let's create a new list. So we have a new list and a main list.
Let's quickly generate some random names. Let's organize this. We, oh, we don't even have to organize it. We know they're somewhere random. We can actually even randomize this. Yeah. I don't know if you know about this, but just for fun, we're gonna go up to data randomized range.
Now we don't really know where we can maybe pick and pot this right. Safiyah are they on the main list? There they are. Oh my God. They're already there. Where's Caitlin. Are they there? Nope, they're not there. And that's probably what you're doing now, but we can do this pretty quickly with match. So one way we can do this is equal match kind of match for set the, where are we gonna match them is in Maine.
and search type. We always wanna do zero there's. Some other ways to do this, but I always put a zero there. Now what the number tells us is it's 1 91. So let's go to Maine 1 91. See if that is true. 1 91. There's Safiyah. Great. Now we can copy and paste this, right? We can. Now we're gonna get an error.
We said did not find value. That's actually really. that's someone that's not on the page. Zim is, and we just keep going. Right. But we can even do a little bit better. If we have a column here, it goes down to 100 and we have this formula. We could double click in the bottom, right corner of the let's get out of this error.
We get click in the double click in the bottom right corner. And now everything is filled in, but now we have one more little tiny issue. Right? We've gotten a, we had a big issue or we didn't know who's on the list. Now we know who's on the list, Bradley pool, Tracy Knights. And sure. You're probably going to, the next thing you're probably going to do is take B an a and sort sort like this.
And there you go. There's everyone on the. There's everyone off the list is the NA, but we could probably tidy this up. You might not want to see NA. So if you just need to grab the names and go, there you go. You have all the names here and you can copy and paste them over to your main. Totally fine. But let's say we don't wanna be copy and pasting.
We just want a, or we do want to copy and paste just the list. We don't have to hunt and Peck here. We could do a filter here, filter range, this column a condition is that column B is over zero and we have the new names. But that's just the names that we're already on there. How do we get a filter of errors?
Well, maybe we don't have an error instead of match. We go if error or around match, we go if error. We do zero. So copy and paste that all the way down. Now we have zeros, so our filter can change to B, to B equal zero. And now these are all of the people that had an error now have zero, but now we have 'em in one column, we can even say something like
this is not new. Add. there we go. And we can copy paste this or cut this to here. Now the formula will change to the, the sheet. So this was a to a and B to B, but now it's a sheet. That's pretty cool. Now we have this formula here, but there's a couple more steps we can do here. Right? Say we just wanna copy paste the list into here and add the new, the new ones.
And then we wanna. them here. If we ever have this, if we ever have a list more than these formulas go, we have to come in here and copy paste this formula all the way down. Well, we don't really wanna do that. How do we fix that? We probably could do an array formula. This is fun for array formula.
Put that around there. And instead of a one, we do a one a, not that. Pretty cool. There is only one formula on this sheet and it is up here on this. It's an array formula and it has done exactly the same thing. Here's all the zeros though. Hmm. So now we're getting zeros when there's nothing there, we gotta fix that.
So we do something like if is blank an inside. is blank. We actually two commas there. We do a one a now let's look down, we still have the zeros and now we have nothing. Wow. So we could actually do something else other than zero here. So instead of a, if error, zero, let's do add. That's pretty cool.
Let's see what happens. Now. We have text that says add and then we have blanks. So now on our add, we need to change this from equals zero to equals ad. Wow. So now we can copy paste any number of names in this new tab, this formula. we'll expand and, and detract and, and ex expand and DEPA and contract as much as needed because of this error, if error and the matching and the blank.
So even if it's a blank, it'll be nothing. If there's an error, it'll say add it. We do match. We'll have a number from the main we know. Okay. Avalon. Well here Kaylin hall is on 12. are they on 12? There they are 12. So this is pretty cool. We've now been able to take sort of hunting and pecking finding each person on the next, on the main page, down to now, we can just copy paste these entire list.
We can even delete the entire list. Then we add anyone we want, we got them. If. There are new names. They will come up with ad and they will be on a separate page so we can just grab them and add them to our main, hopefully, Wallace. This is pretty cool for you. I was excited to get your email and all members are welcome to email me any questions.
Like this is really fun to figure out We now can add people's names that are not on the list to the list. Pretty easy. Right? Once we have that formula set up, the page is set up correctly. One thing I wanna mention is that if you are looking for access to the sheet, if you're looking for access to. A hundred plus more videos and you want to also get into scripting.
Maybe this is the next step. Become a member of BetterSheets.co. You can become a member today. You can also become a member for free. You'll get access to 60 plus videos. If you become a member for $19 a month, you get access to all video. And the sheet that comes with each one. So you get some templates.
If you become a lifetime member today one price you'll never pay again.
I want to thank you for watching this entire video. Leave a comment below that you got to this point, leave a comment and let me know. You got to the end. Thanks for watching.
Watch The Full Video on YouTube