Sheri has two Spreadsheets: One Sheet contains all of the company's current customers and the other contains a generic list of all the top warehouse customers. What Sheri's employer wants is to find out how many customers on the generic warehouse list match current customers of the company. So, how do we set out to accomplish the task? Follow along as Bill shows us the way in Episode #1576.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast.
Are They Customers?
Well, today's question is sent in by Sheri, and this is just going to be a tough one.
Sheri's boss gave her two spreadsheets.
One is a list of all of their customers, and I'm sure there's hundreds of those.
And then another one is a list, that he got from somewhere.
Right so, you know, somebody, some magazines gave a list of all the top warehouse customers or top warehouses or something.
He says. "Hey, figure out which of these peoples, people on this prospect list, is already in our customer list.
And she says. "Is there a function that will do that?" Well, sort of.
Okay, so we want to look for AT&T in this list of customers.
And I have a short list here. I'll press F4 and we want an exact match.
And that tells us that, yes, AT&T is already the second item in the list.
Will copy this down, and what we get is, we get a bunch of numbers, and we get a bunch of N/As.
Anything that has a number is already in that list.
Anything that has an N/A may not be in that list.
And when I say may not be, because this data is pretty good. I typed it all or I had it in a spreadsheet and I actually, inserted a few new customers over here, and this is for something in one of my seminars.
And so I know that, most of this data is perfect, but look IBM and I B M are spelled two different ways.
I threw that in there just to illustrate the problem, and if this came from a magazine, and this came from your accounts receivable department, I guarantee, that there are a whole bunch of things, that do not match.
All right, so in the imaginary perfect world, you could just sort.
These are the, these are the prospects who aren't customers.
These are the prospects who are customers, based on the result of that.
But in real life, in real life, this is not going to be as easy at all.
What I would do in real life is, I would take out the ones that were exact matches and just set those aside.
But then we end up, here let's do it this way.
All right, so these are the ones, that we still need to go through and manually check, to see if they're there.
I would be very tempted to follow this strategy.
I'm going to Ctrl+drag the worksheet to create a copy of it.
So, here's my original customer list.
I'm going to take the prospects, and I'm going to copy those, and I'm going to paste those, below the data.
So, right down here.
And I'm going to put something to differentiate these, like so an x over here .
So, that was a x and Ctrl+Enter and then what I'm going to do is, I'm going to sort the whole thing.
Data AtoZ So, that way now all the customers are appearing alphabetically.
And let's see that sort didn't respect.
Now, that's because I didn't have a heading there, So, just will call this ProspectX.
Make that bold. Let's see if we can sort now.
Data AtoZ Still didn't work.
Microsoft Excel 2007, I hate how you handle this.
So, we want to sort by customer list.
Click OK.
All right so, now we have all the customers alphabetically.
And they're in contrasting colors so, I'd just have to cruise down through here and find the prospect. So I'm going to use Ctrl+Down arrow to move to the next x.
Here CitiGroup, there in blue. Look around, is there a CitiGroup? No.
General Electric. Is there General Electric? GE? Anything? No.
Home Depot and that's HP? No.
IBM. Ah, so there's a match.
All right, so we can you know change that color or say match, or something, just that we will be able to sort it out later.
This is a very imperfect process.
Fuzzy match, Easy-XL offers a fuzzy match.
We can merge sheets and try the fuzzy match.
The problem is, with fuzzy match lots of things, that don't match, get called out as a match.
Or out of the MrExcel challenge of the month, it was a fuzzy match algorithm.
You, then you have to go through, and look at all the fuzzy matches and decide is it the same thing or not.
Even, if I had a thousand in each list, using this method here, just cruising down through and eyeballing each one.
Is it different? Is it the same?
I think is probably the fastest way to go I feel bad for sheri. This is not going to be a fun process, no matter how you do it.
Using match, might get you the few that happen to randomly be entered the same way in the magazine and your customer list but it's still going to ugly, manual process.
Well hey, i want to thank you for stopping by.
Will see you next time, for another netcast from MrExcel.
Learn Excel from MrExcel podcast.
Are They Customers?
Well, today's question is sent in by Sheri, and this is just going to be a tough one.
Sheri's boss gave her two spreadsheets.
One is a list of all of their customers, and I'm sure there's hundreds of those.
And then another one is a list, that he got from somewhere.
Right so, you know, somebody, some magazines gave a list of all the top warehouse customers or top warehouses or something.
He says. "Hey, figure out which of these peoples, people on this prospect list, is already in our customer list.
And she says. "Is there a function that will do that?" Well, sort of.
Okay, so we want to look for AT&T in this list of customers.
And I have a short list here. I'll press F4 and we want an exact match.
And that tells us that, yes, AT&T is already the second item in the list.
Will copy this down, and what we get is, we get a bunch of numbers, and we get a bunch of N/As.
Anything that has a number is already in that list.
Anything that has an N/A may not be in that list.
And when I say may not be, because this data is pretty good. I typed it all or I had it in a spreadsheet and I actually, inserted a few new customers over here, and this is for something in one of my seminars.
And so I know that, most of this data is perfect, but look IBM and I B M are spelled two different ways.
I threw that in there just to illustrate the problem, and if this came from a magazine, and this came from your accounts receivable department, I guarantee, that there are a whole bunch of things, that do not match.
All right, so in the imaginary perfect world, you could just sort.
These are the, these are the prospects who aren't customers.
These are the prospects who are customers, based on the result of that.
But in real life, in real life, this is not going to be as easy at all.
What I would do in real life is, I would take out the ones that were exact matches and just set those aside.
But then we end up, here let's do it this way.
All right, so these are the ones, that we still need to go through and manually check, to see if they're there.
I would be very tempted to follow this strategy.
I'm going to Ctrl+drag the worksheet to create a copy of it.
So, here's my original customer list.
I'm going to take the prospects, and I'm going to copy those, and I'm going to paste those, below the data.
So, right down here.
And I'm going to put something to differentiate these, like so an x over here .
So, that was a x and Ctrl+Enter and then what I'm going to do is, I'm going to sort the whole thing.
Data AtoZ So, that way now all the customers are appearing alphabetically.
And let's see that sort didn't respect.
Now, that's because I didn't have a heading there, So, just will call this ProspectX.
Make that bold. Let's see if we can sort now.
Data AtoZ Still didn't work.
Microsoft Excel 2007, I hate how you handle this.
So, we want to sort by customer list.
Click OK.
All right so, now we have all the customers alphabetically.
And they're in contrasting colors so, I'd just have to cruise down through here and find the prospect. So I'm going to use Ctrl+Down arrow to move to the next x.
Here CitiGroup, there in blue. Look around, is there a CitiGroup? No.
General Electric. Is there General Electric? GE? Anything? No.
Home Depot and that's HP? No.
IBM. Ah, so there's a match.
All right, so we can you know change that color or say match, or something, just that we will be able to sort it out later.
This is a very imperfect process.
Fuzzy match, Easy-XL offers a fuzzy match.
We can merge sheets and try the fuzzy match.
The problem is, with fuzzy match lots of things, that don't match, get called out as a match.
Or out of the MrExcel challenge of the month, it was a fuzzy match algorithm.
You, then you have to go through, and look at all the fuzzy matches and decide is it the same thing or not.
Even, if I had a thousand in each list, using this method here, just cruising down through and eyeballing each one.
Is it different? Is it the same?
I think is probably the fastest way to go I feel bad for sheri. This is not going to be a fun process, no matter how you do it.
Using match, might get you the few that happen to randomly be entered the same way in the magazine and your customer list but it's still going to ugly, manual process.
Well hey, i want to thank you for stopping by.
Will see you next time, for another netcast from MrExcel.
