Jim has two lists that he wants to match, but some records are missing from one list or the other. There are many ways to solve this. Episode 926 will show the classic double-lookup method.
This video is the 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 video is the 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:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
The tips today and this week are from a book called Guerilla Data Analysis using Microsoft Excel, the very first book that I wrote.
Jim sent in this question.
He says, hey, I have 2 worksheets with differing information on both worksheets but there's one field that ties them together.
Now, in Jim's case, it was a serial number.
The data I have here, they're tied together by CUSTOMER name.
So, on my first worksheet, the FORECAST worksheet, you see I have customers and forecast.
I'm going to go into the next worksheet, so that’s CONTROL+PAGEDOWN, and you see that I have customers and orders, and, just like Jim said, he says, some serial numbers are on one sheet, some serial numbers are on the other sheet, and many serial numbers are on both sheets.
Is there some automated way that I can match those up?
Automated?
No.
There's several ways to do this that we're going to talk about this week.
The first method I'm going to use is what I call the double lookup method.
So, we're going to come here to the second list.
You know, actually, I'm going to take the second list and I'm going to copy it onto the first worksheet so they're kind of side by side.
We can see what's happening here.
So, I'm going to go to the second list.
I'm going to add a new column at the end.
It's called THERE, the THERE column, as in is it there, and, here, I can either do a VLOOKUP or a match.
I'll do a match just to illustrate the match.
I want to look for this customer, AT&T, within this list over here, A2 to A21, press the F4 key to put the $ signs in.
At the end of MATCH, we have to put either 0 or FALSE.
I'm going to put a 0 and that's saying we're looking for an exact match.
Copy that down by double-clicking the fill handle.
[ =MATCH(G2,$A$2:$A$21,0) ] Now, the match returns what row it's on which isn't the most useful thing in the whole world, but what I really care about are the N/As.
I want to get all of those N/As together.
That N/A is saying this item is not in the first list, but I'm trying to build a superset of the two lists.
So, if I sort this data by the THERE field, all of the N/As will come to the bottom.
I’m going to take those N/As, I'm going to copy the CUSTOMER names, and paste them to the bottom of the first list, and, then, come over to the FORECAST field and fill those in with 0s.
So, enter 0 there, double-click the fill handle to copy it down, and, basically, we're saying, hey, these customers didn't have a forecast.
Alright.
So, that’s the first lookup, the first lookup step.
The second look up, now, is to go back and get the orders.
We're going to do a VLOOKUP there.
=VLOOKUP.
Look up AIG within this list.
Press F4 again.
I want the second field , 2 , FALSE, and copy that down, alright, and what we're going to see is that for all of the records that were in common, for example AT&T FORECAST of 1.38million, ORDER of 1.37million.
Now, we still have the problem with the N/As over here, so, one more sort.
SORT & FILTER, ZtoA, and I can override all of those N/As with a 0, and then sort again by CUSTOMER.
[ =VLOOKUP(A2,$G$2:$H$21,2,FALSE) ] I now have married these two lists up.
I can see which customers had a forecast and orders, which had only a forecast, and which had orders.
Now, again, this is what I call the double lookup method.
You do one lookup in the second list to figure out which items from the second list are not in the first list, add them to the first list, and then do your real lookup to bring the data over.
Tomorrow, we'll take a look at a different way to do this, a way using pivot tables, which you might find to be a little bit easier and faster.
So, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
The tips today and this week are from a book called Guerilla Data Analysis using Microsoft Excel, the very first book that I wrote.
Jim sent in this question.
He says, hey, I have 2 worksheets with differing information on both worksheets but there's one field that ties them together.
Now, in Jim's case, it was a serial number.
The data I have here, they're tied together by CUSTOMER name.
So, on my first worksheet, the FORECAST worksheet, you see I have customers and forecast.
I'm going to go into the next worksheet, so that’s CONTROL+PAGEDOWN, and you see that I have customers and orders, and, just like Jim said, he says, some serial numbers are on one sheet, some serial numbers are on the other sheet, and many serial numbers are on both sheets.
Is there some automated way that I can match those up?
Automated?
No.
There's several ways to do this that we're going to talk about this week.
The first method I'm going to use is what I call the double lookup method.
So, we're going to come here to the second list.
You know, actually, I'm going to take the second list and I'm going to copy it onto the first worksheet so they're kind of side by side.
We can see what's happening here.
So, I'm going to go to the second list.
I'm going to add a new column at the end.
It's called THERE, the THERE column, as in is it there, and, here, I can either do a VLOOKUP or a match.
I'll do a match just to illustrate the match.
I want to look for this customer, AT&T, within this list over here, A2 to A21, press the F4 key to put the $ signs in.
At the end of MATCH, we have to put either 0 or FALSE.
I'm going to put a 0 and that's saying we're looking for an exact match.
Copy that down by double-clicking the fill handle.
[ =MATCH(G2,$A$2:$A$21,0) ] Now, the match returns what row it's on which isn't the most useful thing in the whole world, but what I really care about are the N/As.
I want to get all of those N/As together.
That N/A is saying this item is not in the first list, but I'm trying to build a superset of the two lists.
So, if I sort this data by the THERE field, all of the N/As will come to the bottom.
I’m going to take those N/As, I'm going to copy the CUSTOMER names, and paste them to the bottom of the first list, and, then, come over to the FORECAST field and fill those in with 0s.
So, enter 0 there, double-click the fill handle to copy it down, and, basically, we're saying, hey, these customers didn't have a forecast.
Alright.
So, that’s the first lookup, the first lookup step.
The second look up, now, is to go back and get the orders.
We're going to do a VLOOKUP there.
=VLOOKUP.
Look up AIG within this list.
Press F4 again.
I want the second field , 2 , FALSE, and copy that down, alright, and what we're going to see is that for all of the records that were in common, for example AT&T FORECAST of 1.38million, ORDER of 1.37million.
Now, we still have the problem with the N/As over here, so, one more sort.
SORT & FILTER, ZtoA, and I can override all of those N/As with a 0, and then sort again by CUSTOMER.
[ =VLOOKUP(A2,$G$2:$H$21,2,FALSE) ] I now have married these two lists up.
I can see which customers had a forecast and orders, which had only a forecast, and which had orders.
Now, again, this is what I call the double lookup method.
You do one lookup in the second list to figure out which items from the second list are not in the first list, add them to the first list, and then do your real lookup to bring the data over.
Tomorrow, we'll take a look at a different way to do this, a way using pivot tables, which you might find to be a little bit easier and faster.
So, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
