In the spirit of "VLOOKUP WEEK 2012" [march 25th - March 31st, 2012], Bill centers on VLookup again today. In theory, I sent my workbook to my manager. I have received the workbook back from my manager and now I need compare the data [the original data with the new data] to find out what was deleted, what was added and what was changed. Today Bill shows us how to work with VLOOKUP to find the answers to our questions.
Welcome toVLOOKUP WEEK 2012! What is VLOOKUP WEEK, you ask? VLOOKUP WEEK is an entire week [March 25th, 2012 through March 31st, 2012] dedicated to one of the greatest yet least used Functions of Microsoft Excel. Check out the VLOOKUP WEEK 2012 Blog!
Welcome toVLOOKUP WEEK 2012! What is VLOOKUP WEEK, you ask? VLOOKUP WEEK is an entire week [March 25th, 2012 through March 31st, 2012] dedicated to one of the greatest yet least used Functions of Microsoft Excel. Check out the VLOOKUP WEEK 2012 Blog!
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 1534: Compare 2 Lists Well, hey, welcome back.
We’re continuing VLOOKUP Week here.
This is our second podcast this week about VLOOKUP classic problem.
So we have this original data here and we sent this data out to our manager.
And we were working on this data and the manager was working on the data and now we need to compare the 2 lists.
There’s always 3 questions.
Question A: What did the manager delete?
What did the manager add?
And then, what values did the manager change?
Alright, so to figure out what was deleted, we go back to our original list, the list that we started with, =VLOOKUP.
Look for that code, code A, in this list – in this list only has to be 1 column, F4,1,False.
And what we’re trying to get here is – So the A, the fact that the A came up again means it’s still there, it’s the N/A. So any N/As that we find are items that have been deleted, alright?
So, that’s our first thing that we’re looking for.
What did you delete?
Anything that has an N/A has been deleted by the manager.
Now, what did he add?
I’m going to add a column here called Add=VLOOKUP, look for this code A over our original list.
F4,1,False – the 1 says we’re looking for the first column.
And again, very similar process here, anytime that we have an N/A that’s something that he must have added.
Alright now, a couple of cool tricks here.
If I go to this column and I Sort Ascending, the items that were deleted go to the bottom of the list.
Over here, the items that were added, I want to bring those to the top of the list so we Sort Z to A Descending and they come up.
Alright now, for the things that are in both lists, we want to see if he changed any values.
I’m going to insert a new column, let’s call this New Val=VLOOKUP, go look up code A and this list, press F4,2,False.
And shoot to copy that down.
And what we need to do is we need to kind of scan through there and look for things that are different or we can even do conditional formatting.
Alt+O+D, so we want to create a new rule, use a formula and this rule has to be written as if we’re talking about C4.
So, =C4 not equal to B4 and we’ll just format that in red font there, click OK, click OK, click OK.
Alright, and then anything that shows up in red has been changed.
The value has changed.
Click those red cell, right-click, Sort, and we will Put Selected Font Color On Top.
Alright, so now right here, the 3 items that were added, down here the 3 items that were deleted and here’s the 3 items that were changed.
All of that done with VLOOKUP.
Well, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
We’re continuing VLOOKUP Week here.
This is our second podcast this week about VLOOKUP classic problem.
So we have this original data here and we sent this data out to our manager.
And we were working on this data and the manager was working on the data and now we need to compare the 2 lists.
There’s always 3 questions.
Question A: What did the manager delete?
What did the manager add?
And then, what values did the manager change?
Alright, so to figure out what was deleted, we go back to our original list, the list that we started with, =VLOOKUP.
Look for that code, code A, in this list – in this list only has to be 1 column, F4,1,False.
And what we’re trying to get here is – So the A, the fact that the A came up again means it’s still there, it’s the N/A. So any N/As that we find are items that have been deleted, alright?
So, that’s our first thing that we’re looking for.
What did you delete?
Anything that has an N/A has been deleted by the manager.
Now, what did he add?
I’m going to add a column here called Add=VLOOKUP, look for this code A over our original list.
F4,1,False – the 1 says we’re looking for the first column.
And again, very similar process here, anytime that we have an N/A that’s something that he must have added.
Alright now, a couple of cool tricks here.
If I go to this column and I Sort Ascending, the items that were deleted go to the bottom of the list.
Over here, the items that were added, I want to bring those to the top of the list so we Sort Z to A Descending and they come up.
Alright now, for the things that are in both lists, we want to see if he changed any values.
I’m going to insert a new column, let’s call this New Val=VLOOKUP, go look up code A and this list, press F4,2,False.
And shoot to copy that down.
And what we need to do is we need to kind of scan through there and look for things that are different or we can even do conditional formatting.
Alt+O+D, so we want to create a new rule, use a formula and this rule has to be written as if we’re talking about C4.
So, =C4 not equal to B4 and we’ll just format that in red font there, click OK, click OK, click OK.
Alright, and then anything that shows up in red has been changed.
The value has changed.
Click those red cell, right-click, Sort, and we will Put Selected Font Color On Top.
Alright, so now right here, the 3 items that were added, down here the 3 items that were deleted and here’s the 3 items that were changed.
All of that done with VLOOKUP.
Well, hey, I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
