BackIn2Shape at YouTube has two worksheets; one of customers, and one of prospects. If a prospect is on the customer list, he would like to delete it. Episode 1109 show you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com and I've got a cool Excel tip for you today.
Mike: Hey, this is Mike Gel Girvin from Excel Is Fun in YouTube and I have a different way to do that.
Bill: Okay, hey, it's another Dueling Excel podcast, I'm Bill Jelen from MrExcel, Mike Girvin from Excel Is Fun is going to be joining us.
Someone sent this question in via YouTube.
They have a workbook, two worksheets, a Prospects worksheet and a Customers worksheet and they say: hey, I want to look through the Prospects worksheet, if the prospect is already on the Customers worksheet, 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 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.
So now I can see both worksheets 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 Vlookup, I'm going to use Match.
=MATCH(A2,Customers!$A$2:$A$21,0), say: go find this customer in this list over here, on the other sheet, check that out, isn't that cool, you can just point to it using the mouse, press F4 to lock that down and then “,0”, that's like using “,FALSE” in the Vlookup.
And you know, FALSE is really a 0, in the Match they put a 0 there, because there's the possibility of doing less than or greater than.
And Match – that's a topic for another day.
#N/A, all right, usually #N/A is bad, but in this case #N/As 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, #N/A means it's not in the other list, anything else means that it is in the other list.
So what is this saying?
5: General Motors is the 5th item, one, two, three, four, 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 to Data, click A-to-Z and then delete those records, bam, they’ll be gone.
That said, that's how I would do it.
Now, of course, the downside with mine, and I'm not sure that Mike is going have any better solution for this, is: if there's 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.
All right, Mike, let's see what you can do.
Mike: Thanks, MrExcel.
Hey, Fuzzy Match, wow, that gets pretty complicated.
But no worries, you go search for Fuzzy Match on the MrExcel message board, there's 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 Vlookup and see how that works.
=VLOOKUP(A2,Customers!$A$2:$A$21,1,0) we need our lookup_value, hey, we're going to say: hey, AIG, comma and then I'm going to say: where is the table_array?
Well, instead of a table, we'll 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 2, 3, 4 like we usually use, we'll just use 1.
1, 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 #N/A, so it works just fine.
FALSE, because we're looking up a word, so instead of FALSE I'm going to put a 0, Ctrl+Enter, double click and send it down.
Now, in 2007, when you want to sort, you can right click, Sort and I'll say A to Z.
And sure enough, all of the #N/As mean they are not over here.
These are the ones we want to keep, these are the ones we want to do… whatever with.
Ah, Match delivered the position, right?
But the Vlookup actually returned the customer name.
Now, I'm going to Ctrl+Z to unsort that.
And now, I want to show you a third formula here, but I want to close this open window here.
Notice there's a “1” there and a “2” there, that means that 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 a Countif.
And I want to show you something interesting.
Between using a sheet reference, where you click on the sheets and using that open same workbook open in two windows.
Alright, Countif, no, wait a second.
What Countif?
Well, Countif, what if we say: hey, is AIG… how many AIGs are over in this list?
Well, if it's there, it'll return a 1.
If AIG is not over there, it'll return a 0.
So we'll get a columns of 1s and 0s. 0s will be the return value that we're interested in.
=COUNTIF, I'm going to say the range and now watch this, 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 thinks we want sheet references everywhere.
We definitely don't want Prospects, because of the formulas on this sheet, so we have to highlight it and delete it.
=COUNTIF(Customers!$A$2:$A$21,A2).
That will work just fine, you know, you double click and send it down.
But let's just delete that and open the same workbook in a new window, and watch the difference when we create a formula with Countif.
Arrange All, we want Vertical, click OK.
So, and then I click over here and I'm actually going to scoot 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 and Maximize buttons and put them wherever you want.
So there we have it, I'm going to say =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 of it, because we have the same workbook open in two windows, we don't get that annoying “Prospects!” sheet reference.
Ctrl+Enter, double click and send it down.
And 0s are the ones we are interested in, I want to shove the 1s to the top, the #N/As 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 0s, Vlookup delivered an #N/A, Match delivered an #N/A, these are the ones we are interested in.
All right, I'll throw it back to MrExcel.
Bill: Alright, there you have it.
I want to thank everyone for stopping by, let's see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
Mike: Hey, this is Mike Gel Girvin from Excel Is Fun in YouTube and I have a different way to do that.
Bill: Okay, hey, it's another Dueling Excel podcast, I'm Bill Jelen from MrExcel, Mike Girvin from Excel Is Fun is going to be joining us.
Someone sent this question in via YouTube.
They have a workbook, two worksheets, a Prospects worksheet and a Customers worksheet and they say: hey, I want to look through the Prospects worksheet, if the prospect is already on the Customers worksheet, 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 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.
So now I can see both worksheets 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 Vlookup, I'm going to use Match.
=MATCH(A2,Customers!$A$2:$A$21,0), say: go find this customer in this list over here, on the other sheet, check that out, isn't that cool, you can just point to it using the mouse, press F4 to lock that down and then “,0”, that's like using “,FALSE” in the Vlookup.
And you know, FALSE is really a 0, in the Match they put a 0 there, because there's the possibility of doing less than or greater than.
And Match – that's a topic for another day.
#N/A, all right, usually #N/A is bad, but in this case #N/As 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, #N/A means it's not in the other list, anything else means that it is in the other list.
So what is this saying?
5: General Motors is the 5th item, one, two, three, four, 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 to Data, click A-to-Z and then delete those records, bam, they’ll be gone.
That said, that's how I would do it.
Now, of course, the downside with mine, and I'm not sure that Mike is going have any better solution for this, is: if there's 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.
All right, Mike, let's see what you can do.
Mike: Thanks, MrExcel.
Hey, Fuzzy Match, wow, that gets pretty complicated.
But no worries, you go search for Fuzzy Match on the MrExcel message board, there's 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 Vlookup and see how that works.
=VLOOKUP(A2,Customers!$A$2:$A$21,1,0) we need our lookup_value, hey, we're going to say: hey, AIG, comma and then I'm going to say: where is the table_array?
Well, instead of a table, we'll 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 2, 3, 4 like we usually use, we'll just use 1.
1, 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 #N/A, so it works just fine.
FALSE, because we're looking up a word, so instead of FALSE I'm going to put a 0, Ctrl+Enter, double click and send it down.
Now, in 2007, when you want to sort, you can right click, Sort and I'll say A to Z.
And sure enough, all of the #N/As mean they are not over here.
These are the ones we want to keep, these are the ones we want to do… whatever with.
Ah, Match delivered the position, right?
But the Vlookup actually returned the customer name.
Now, I'm going to Ctrl+Z to unsort that.
And now, I want to show you a third formula here, but I want to close this open window here.
Notice there's a “1” there and a “2” there, that means that 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 a Countif.
And I want to show you something interesting.
Between using a sheet reference, where you click on the sheets and using that open same workbook open in two windows.
Alright, Countif, no, wait a second.
What Countif?
Well, Countif, what if we say: hey, is AIG… how many AIGs are over in this list?
Well, if it's there, it'll return a 1.
If AIG is not over there, it'll return a 0.
So we'll get a columns of 1s and 0s. 0s will be the return value that we're interested in.
=COUNTIF, I'm going to say the range and now watch this, 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 thinks we want sheet references everywhere.
We definitely don't want Prospects, because of the formulas on this sheet, so we have to highlight it and delete it.
=COUNTIF(Customers!$A$2:$A$21,A2).
That will work just fine, you know, you double click and send it down.
But let's just delete that and open the same workbook in a new window, and watch the difference when we create a formula with Countif.
Arrange All, we want Vertical, click OK.
So, and then I click over here and I'm actually going to scoot 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 and Maximize buttons and put them wherever you want.
So there we have it, I'm going to say =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 of it, because we have the same workbook open in two windows, we don't get that annoying “Prospects!” sheet reference.
Ctrl+Enter, double click and send it down.
And 0s are the ones we are interested in, I want to shove the 1s to the top, the #N/As 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 0s, Vlookup delivered an #N/A, Match delivered an #N/A, these are the ones we are interested in.
All right, I'll throw it back to MrExcel.
Bill: Alright, there you have it.
I want to thank everyone for stopping by, let's see you next time for another Dueling Excel podcast from MrExcel and Excel Is Fun.
