Joe:      Hey everybody. Joe McCall here. Alex and I were just online on Skype a minute ago and we're going to record a Q and A but either his computer kept on crashing or mine kept on crashing. So we just said “Let's reboot our computers. Let's just start over again. But he's getting ready to go to a trip so he doesn't have time to re-do the video. So what I said I'll do is I'll go ahead and do this video.

Now, here's the question. He's got a-this is a common question I get a lot. He's got a spreadsheet of all of the people that he's mailed to recently and he just downloaded a new spreadsheet of a bunch of new people that he wants to mail to and he wants to take out of the new spreadsheet all of the names or the addresses of the homes that he mailed to a couple of weeks ago. So one of the ways that I do this-and I'm sure there are better ways out there-is through a formula in Excel called “V Look up.” V for Vertical Lookup.

So I'm just going to walk through how to do that real quick because I know this is a common question a lot of people have and so let me show you how to do this.

First I'm going to say is, whenever you're in excel and you have a question about something, go to the help window. So many people kind of forget about help or don't really take it seriously. But if you just go to excel and look up “V lookup,” something's going to come up here.

Let me show you what else you can do. There's a website called “YouTube.” It helps when you put in the dot com. The “YouTube” channel, remember that episode in the office where, what's his name-Michael-is wanting to get, he's doing some kind of party at their office and he's trying to get a hold of “Youtube” to get them to come and film the office party and he couldn't figure out. Nobody at “Youtube” was answering the phone. So anyway, go to “Youtube.” Do a search here for “V Lookup Tutorial. Okay, V Lookup Excel tutorial.” You're going to find a bunch of videos here or the bunch of ads. But here, V Lookup tutorials. Real simple stuff that you can look at. Now here's, from Excel, my window didn't close. If you click on here in Excel, you'll see the tutorial they have, you see the tutorial they have here on excel and excel for the V Lookup.

So I accidentally clicked on the outlook ad here. Let me go back. So here is John Flynn-3 years ago-even though this video is 3 years old, I can guarantee you it’s still useful for V Lookup.

So here I have 2 spreadsheets. I have the old list and the new list. Let me move this up here. So old list, new list. What I did is I inserted a new column here. Next to on this new list, I'm going to look to see if these addresses in this column have been in the older spreadsheet in this column. So all I'm going to do here is type in “Equals V lookup,” and you can see here what it’s looking for. Let me zoom in a little bit more. Zoom in a little bit more. Alright so, I'm going to do what's called a V lookup. It’s going to ask for the look up value and then the table, the column index and then I don't know what range look up means but I'll show you what I do here.

So I'm going to say “Look up this cell” and do a comma and this sheet. I'm going to scroll all the way down to the bottom here. Alright, so I have all of the cells. You can see the table right there and I'm going to do comma and I want the column index number to be 1. I want to give me the results in that table from column 1 and then I do “False.” Why a false, I don't know. So if it comes up, if it brings in-it’s kind of hard to explain. Just looking for this value in that other table that I highlighted and if it’s there, to give me the result in column 1 and if it doesn't, if it’s not there then do “False.” So when it brings in that address, that means it’s in the other spreadsheet. Does that make sense?

Now, one thing I'm looking at here is, I have to make sure that other table that I'm referencing in has a dollar sign in it, because what I'm going to do now is I'm going to copy this formula all the way down. So I'm going to label this here “In other spreadsheet question mark.” Let me just make this a different color. So it’s this cell and the other spreadsheet. Sometimes what I'll do is I'll insert a new column here and I'll do an if-then statement and I'll say “If equals if this equals that” then say “Yes.” If not, do no.” Not a big deal here.

So what I'll do is bring this down and so if it’s an if statement, if this equals that, then say yes. If not, equal no. So then I'll take these and I'm going to copy them all the way down and there's a shortcut. By the way, here's one that's not because it has an N A which is not applicable. So I'm going to take this. The shortcut to copy all the way down-it this works-if I go to the lower right corner and it turns into a crosshair, I'm just going to double click on it and it brings it all the way down. Let me go down here. So just copy that formula all the way down. Again, to do that, you highlight your last thing and you double click on that lower right hand corner and it copies it down.

So I'm just scrolling down here and it looks like, that looks pretty good right? I'm going to double click and just confirm the formula. So this is looking for this cell in this spreadsheet column B and it brings me back column, whatever the value is in column 1, otherwise its false.

So keeps on scrolling down and it’s got a lot of them that are not in there now. So I'm going to go to the bottom. Just make sure that, I'm at the bottom now of this spreadsheet and I can see all of the N A's here and the yes'. So I'm going to back up to the top and I'm going to do what is called a “Filter” here. Just click “Create a filter” and I'm going to say “Bring me all of the Yes' or actually all of the N A's.” So I filter out the N A's. So I uncheck “Yes.” It takes a while since it’s a big spreadsheet. There we go. So now, this is a filtered view and these are all of the addresses that are not in the other spreadsheet. So what I'll typically do is I'll just create a new spreadsheet, new workbook or whatever it’s called. Come in here, and I'm going to just scroll down and copy all of these rows. I'm going to copy that, I did the edit, I mean I did a command C or Control C and copy. It’s a lot of info so my beach ball is spinning.

By the way while this is doing this. If any of you have a better way to do this, let me know because I'm sure there is. I know if you're familiar with databases or access, there's really fast easy ways to do these. Go to workbook. I'm going to go to my new spreadsheet and just paste those in. Alright, and at the top I believe of my spreadsheet and scroll over to the right to get to the address was there it is. Let's do this, I'm going to Window Freeze Pane so that the Window the top row always stays at the top there. Okay, I'm gonna go down all the way, let's do this. Alright, so I have 6517 records that are not in the other spreadsheet. Okay, that's it. So now I have-just to recap-I have three spreadsheets here. The first one was the oldest. This is the list that we mailed to, Alex mailed to a couple of weeks ago. We have the new list and I have the filter on-let me get up to the top here. I have the filter on where these are the N A's. I just copy those into a new spreadsheet. I copied all of these in here. These are the addresses that is not mailed to yet. Cool! Does that make sense?

So I'll copy this spreadsheet in there with only a few rows of data so you can look at this formula and copy it, okay? But again, go to Excel help and look that up there. Go to YouTube and look how the V Lookup formula works. But you'll probably get a better tutorial than what I just did. But I use this a lot. I use it a lot to take out of one spreadsheet something that's in another spreadsheet. Okay, thanks.