Hey, welcome to part two. We're counting domains in a list of emails. Over the course of this video, we're gonna be doing a couple of formulas that will help us clean up the columns and also get some more information from the list. Will also be saving the data as values sort of copy and pasting values, but also saving these.

Sheets as months go by. So maybe we wanna do this 12 times a year, once a month, and then be able to summarize the differences and data as it goes month to month. But we don't wanna have to do this all again. So to begin, one of the interesting things here is when we have a la, a large list and it's only gonna get larger.

We can add a, at the, I'm gonna do command down arrow and go, just go down bottom. We have 270, or maybe if we have a newsletter subscriber list, or maybe an outreach list that goes into the thousands. Five to six to seven, 10,000 goo Google Sheets can definitely handle this. What we're gonna do is, I'm gonna clean it up a little.

I'm gonna delete everything over here on the right. And typically you might want to also delete all of these column rows at the bottom, but we wanna leave them. In fact, we might want to add more, but every time we do that, we have to come back here and do this split function again and drag us down.

But we don't want to, well watch what happens when I give it you know, no values, right? It, it's, as you know, it's empty. So what we could do and what we will do, and if. Error formula around this into comma and nothing. And so now there's an error. Nothing will happen, but we also don't have anything there for when we bring it into the count.

Nothing changes here. Now what happens on the count is a little d.  you see here? Just count. If there's a blank, there's nothing. So it has zero. We don't want a bunch of zeros, so, but if error, if we do, let's do it. If error is not gonna work here, there's no error. This is zero. So what we use here instead is the if formula, and then the logical expression that we wanna find a true false for is, is blank.

Now this this formula allows us to put a value in and we're gonna do a 1 63, is that, that cell there? So if it's blank, it'll be.  and it's gonna do the first value so that the first value is gonna be nothing. It's gonna be a point where we're gonna do two commas. So in between those two commas do that.

If it's true, if it's false, then do the count. If so, let's see what happens now. And now we have a blank, right? And so what we can do then, now it's blank there. So now we don't have a number. And in this short formula, , we don't have any of those zeros. So if we had, let me do that again without the, if we had just zeros here, interesting.

Is that this sort column is gonna have a bunch of zeros and so we get rid of that as well and we can do it all the way to the top.

and it doesn't change any of these values. Cool. So that's it if and is blank. Now if we wanna get some information from this list, say we have these domains, but we actually do want to see which email addresses we have at each of these domains. Now on this short page. Here, let me scroll up to the top.

I have a few here. Five of make use of. So one thing we can do is a filter, right? And we can say filter. The range is gonna be let's say it's the split column. It's gonna be a, and we want to filter that, this so split.  C to C, the C column is equal to this cell. Let's see what happens there. And so what happens here is we get all five of these, but if we try to put this anywhere else, we get this re reference.

It's error, result error. And it's what it is, is it can't write over anything else. . So we have a real problem here. We, well, one funny solution is we can actually transpose this. And so now these are all in this filter to the right

now. Isn't that funny? So now we have these, these columns, but that's. . Very good, right? If we have 39

here, so what we need to do is we need to figure out a way to see these. Now one thing is we couldn't put this one. One interesting thing is which I won't go into right now, is one solution is we can make a new sheet where this all, all these domains are in a dropdown menu. And then as we select it, we change this this here, but we don't necessarily see everything next to each other.

We wanna see it right here, right? For just in this particular problem. We wanna see everything right next to each other. So what we do with this, instead of transposing, you're gonna do joint, okay? So we're gonna use a joint instead of say let's say we joined it with some hyphens, right? Join that, some hyphens.

We get this still to the right kind of problem, like have these hyphens in between. And that's not very easy to.  what we want to do, instead of hyphens, we want to let's delete that command. Enter gives us this a force enter, and now they're all within this one cell. Now this only really works when it's, you know, just this few, right?

Seven. As we, if we get up to 39, it is not gonna be very pretty or easy to work with. This is hard to manage. . So this only really works if you only have a few to look at. But for in this particular case, we wanted to see, you know, what are these the, the sort of style of these new emails. Maybe we want to email someone else at Future net.

And so now we know it's just first name dot last name. And we can do this down this entire thing. And we can see all of those different types of things. We can see, okay, at this domain, here's who is available and next I'm gonna save this data.

So, just as a bonus, just before we go on to the next spot, I wanted to save this join somewhere. And I did wanna actually show you how to do the data validation really quick. Gonna duplicate this, and we're gonna be doing this for the next up of duplicating these sheets. This duplicated sheet literally doesn't change anything.

It all still works. But what we're gonna do, instead of showing these all together, just take one of them, that formula, delete all this. And we just wanna say, okay, pick one to this. Let's make it a, we'll give it a little room. I'm gonna show you how this data validation works. So we want to right click data validation and it's then we're gonna do a list from a range, and we're just gonna do a to a

save it. Now we can select any of these domains and as we select them, we're gonna have this filter, function. Filter. Yeah, actually. But instead of join, we're just going to do the filter and instead of a, the, the a eight here, this cell, we're gonna do what's in the cell, D four. And so now as we switch through, you can see all the different things.

So we, we can do this if you wanna make the data sort of interactive here. So I'll call this sort.

The next problem we wanna solve is that maybe we want to save this data month to month. So I'm gonna delete column C here. Actually, I'm gonna delete every column to the right. Let's make this simpler. I'd like to do that cleanup sheets. And what we can do now is duplicate. And again, we're not, we're not losing anything here.

And I'm gonna call this like, say to this, I think this. Let's say this month was January. We'll call this January, 2020. And in order to say this, right, if we made a different list right here, if we added this list, now all of these formulas would change and we'd get a different number. But we wanna, we wanna take a snapshot of what this month is like today.

And so what we do is we just take.