Bill Jelen: Hey, I'm Bill Jelen from MrExcel.com and I've got a cool Excel tip for you today.
Mike Gel Girvin: Hey, this is Mike Gel Girvin. Excel is fun in YouTube and I have a different way to do that.
Bill Jelen: Okay, hey it’s another dual in Excel podcast. I'm Bill Jelen from MrExcel. Mike Girvin from “Excel is Fun” who’s going to be joining us. Someone sent this question via YouTube. They have a workbook, two worksheets, a prospect’s worksheet and a customer’s worksheet. And they say “Hey, I want to look through the prospect’s worksheet, if the prospect is already on the customer worksheets, let's delete them.” Okay well, I'm going to do that using match but first I want to do view new window and then arrange all that. It allows me to see two different windows of the same spreadsheet. On the left hand side, here are the prospects. On the right hand side check this out, I can click and see the customers.
Now I can see both worksheet side by side, isn’t that cool and then I'm going to create a brand new column called “There” as in “Is It There”. We could use view lookup. I'm going to use equal Match. Say, go find this customer in this list over here on the sheet. Check that out, isn't that cool? You're just going to point to it using the mouse, press F4 to lock that down and then comma zero. That’s like using comma false and if you look up, you know, false is really a zero in the match. They put a zero there because there is the possibility doing less than or clear that match. That’s a topic for another day.
NA or usually NAs are bad but in this case NAs are good that means that it is not over there. We will double click to shoot that down. All right, here’s what we get. NA means its not in the added list anything else means that it is in the other list, so what is this thing five general motors is the fifth item, five in that list. I don’t care where it is. I just want to know if it’s there or not and so we can come back here at the data. Click A to Z and then delete those records. They’d be gone and so that’s how I would do it.
Now of course, the downside I would mind and I'm not sure that Mike’s going to have any better solution for this is if there is a misspelling, if it says General Motors over here and GM over there. It’s never going to find that we have to look at a fuzzy match formula. That’s definitely a story for another day. Alright, Mike let's see what you can do.
Mike Gel Girvin: Thanks MrExcel, hey fussy match. Wow! That gets pretty complicated but no worries. You go search for fussy match with the MrExcel message ports. There's a lots of great solutions. Now here match, that’s a great way to go but what if you don’t know how to use the match. Let's just use the V lookup and see how that works. V lookup we need our lookup via, “Hey”, were going to say “Hey, AIG,” and then I'm going to say, “Where is the table array?” Well, instead of the table just give it a column just like that, hit my F4 key to lock it and then we have our comma to get to the column index and instead of two, three, four like were usually use, we’ll just use one.
So we’re looking up through the actual lookup column and returning the item from that column. Now really that doesn’t make sense usually except for in this case we’re only interested in the NA so it works just fine. False because were looking up a word, so instead of false so I'm going to put a zero, control enter. Double click and send it down.
Now in 2007, when you want to sort you can right click sort and I will say A to Z and sure enough all of the NAs mean they are not over here. These are the ones we want to keep. These are the ones we want to do whatever with. Match deliver the position right but the V lookup actually return the customer name.
Now I'm going to control Z to un-sort that and now I want to show you our third formula here but I want to close this open window here. Notice there is a one there and a two there that means with the same workbook is open twice. I'm going to go ahead and close this and then maximize. I want to build this formula with account if and I want to show you something interesting between using a sheet reference where you click on the sheets and use in that same workbook open in two windows.
Alright, countif, now wait a second, what countif. Well countif, what if we say “Hey, how many AIGs are over in this list.” Well if it’s there it will turn to one, if AIG is not over there it will return to zero. So we get columns of ones and zeros. Zeros will be the return value that we’re interested in, countif. I'm going to say the range and now watch as I'm clicking on the sheet. I'm highlighting the customer names. I'm hitting F4, I'm going to type comma and then I'm going to click on this sheet right here and then I'm going to go ahead and get that and what happened this annoying sheet reference as soon as we start using sheet reference the formula I think we want sheet reference is everywhere.
We definitely don’t want prospects because the formula is on the sheet so we have to highlight it and delete it. That will work just fine. You know you’ve double click and send it down but let's just delete that and open the same workbook in the new window and watch the difference when we create a formula with countif. Arrange all, we want vertical, click okay.
So and then I click over here and I'm actually going to scoop this out of the way and actually once you open the two windows you actually don’t need to use that arrange. You can just use the restore or maximize button and put them wherever you want.
So there we have it. I'm going to say equals countif. The range I'm doing the same thing as I did before, F4 comma but watch this when I click right there you got to be kidding me. It didn’t put it in awesome, totally gets rid off because we have the same workbook open in two windows. We don’t get that annoying prospect explanation point sheet reference, Ctrl enter, double click it and send it down.
And zeroes are the ones we’re interested in. I want to shove the ones to the top and NA to the bottom. So I'm going to click in one cell and right click, sort and I'm going to do largest to smallest. So there we have it. Countif deliver the zeroes, V lookup deliver the NA, Match delivered the NA. These are the ones we are interested in. Alright, threw back to MrExcel.
Bill Jelen: Alright, there you have it. I want to thank everyone for stopping by. I’ll see you next for another dueling Excel podcast from MrExcel and ‘Excel is Fun.’
Transcription by:
Scribe4you Transcription Services