Today, with Episode #1343, Bill continues to show us how to Compare our worksheets - side by side - using a more involved technique. With a few minor modifications and the use of a VLOOKUP, comparing data becomes a bit more permanent and efficient.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1343: Compare Sheets Part 2.
In yesterday's netcast I had a question from James. James had these two different sheets, United States and Denmark and he wanted to compare the values on these two sheets and yesterday I just did compare side-by-side.
Probably it's not going to work most of the time because the rows are going to be out of sequence or something like that. So what I'm going to do is, I'm going to actually do a Vlookup to get this data from one side to the other.
First thing I want to do is, I need to get rid of the merged cells. Select all cells and then Ctrl+1, go to Alignment and you see Merge Cells here is neither checked nor unchecked.
It's in this tri-state, the kind of mixed state. So 1, 2 clicks will get it back to unmerged and then let's see up here; we're going to add above all of these cells the word USA and copy that then out here to the right and in these cells type Denmark. Ctrl+Enter.
Okay, now what I want to do is a Vlookup here.
Vlookup. Go look up this value over in column B and our lookup table, I'm going to click on the Denmark sheet and choose all of my data, Ctrl+Shift+Right Arrow, Ctrl+Shift+Down Arrow. I want to press F4 to put dollar signs in that address comma and right now I need a 2 and if I was smart I would have put a 2 up in row one so that way I could copy it over but I wasn't smart.
So instead of typing a 2 here I am going to ask for the column of B1. Column B 1 and then finally comma false saying we want an exact match no close matches and we'll press Enter.
=VLOOKUP(B5,Denmark|$B$5:$K$124,COLUMN(B1),False) Good, except for what I really needed-- I didn't need column 2, I needed column D which would be the third column. So let's come in here and change from column B1 which is the second column to column of C1.
The formula =VLOOKUP(B5,Denmark|$B$5:$K$124,COLUMN(C1),False) Now this is just frankly me being lazy. I should type the number 2 in there and then the number 3 and then the number 4, number 5 and so on but I use that little trick here for just a quick and dirty analysis so I don't have to type those various values in. Let’s see, Ctrl+V.
Okay what did I do? I missed the dollar sign here; dollar sign before the B. It looks like we're starting to get some good values. All right, so that's a good thing. At this point I would probably convert these formulas into values.
Ctrl key V. That was Ctrl+C if you're in Excel 2010 and that little key there on the right hand side between the Alt and the Ctrl, the program key. Program key V; fastest way to convert those otherwise of course use Home, Copy and then Paste and Paste Values. Whatever works for you and there you go. Something a bit more permanent. Now you're still going to probably have to use freeze panes here so that way you can see each value close to each other.
A little bit more permanent than using the method from yesterday, the compare side-by-side.
You actually get those values on the same spreadsheet. Now if you needed to do some calculations like comparing delta's or something like that you could pull that off with this method. So probably in my opinion a better way to go, more permanent, gets more numbers on the same worksheet, lets you do calculations and you can even sort those together. A good topic for another day.
I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1343: Compare Sheets Part 2.
In yesterday's netcast I had a question from James. James had these two different sheets, United States and Denmark and he wanted to compare the values on these two sheets and yesterday I just did compare side-by-side.
Probably it's not going to work most of the time because the rows are going to be out of sequence or something like that. So what I'm going to do is, I'm going to actually do a Vlookup to get this data from one side to the other.
First thing I want to do is, I need to get rid of the merged cells. Select all cells and then Ctrl+1, go to Alignment and you see Merge Cells here is neither checked nor unchecked.
It's in this tri-state, the kind of mixed state. So 1, 2 clicks will get it back to unmerged and then let's see up here; we're going to add above all of these cells the word USA and copy that then out here to the right and in these cells type Denmark. Ctrl+Enter.
Okay, now what I want to do is a Vlookup here.
Vlookup. Go look up this value over in column B and our lookup table, I'm going to click on the Denmark sheet and choose all of my data, Ctrl+Shift+Right Arrow, Ctrl+Shift+Down Arrow. I want to press F4 to put dollar signs in that address comma and right now I need a 2 and if I was smart I would have put a 2 up in row one so that way I could copy it over but I wasn't smart.
So instead of typing a 2 here I am going to ask for the column of B1. Column B 1 and then finally comma false saying we want an exact match no close matches and we'll press Enter.
=VLOOKUP(B5,Denmark|$B$5:$K$124,COLUMN(B1),False) Good, except for what I really needed-- I didn't need column 2, I needed column D which would be the third column. So let's come in here and change from column B1 which is the second column to column of C1.
The formula =VLOOKUP(B5,Denmark|$B$5:$K$124,COLUMN(C1),False) Now this is just frankly me being lazy. I should type the number 2 in there and then the number 3 and then the number 4, number 5 and so on but I use that little trick here for just a quick and dirty analysis so I don't have to type those various values in. Let’s see, Ctrl+V.
Okay what did I do? I missed the dollar sign here; dollar sign before the B. It looks like we're starting to get some good values. All right, so that's a good thing. At this point I would probably convert these formulas into values.
Ctrl key V. That was Ctrl+C if you're in Excel 2010 and that little key there on the right hand side between the Alt and the Ctrl, the program key. Program key V; fastest way to convert those otherwise of course use Home, Copy and then Paste and Paste Values. Whatever works for you and there you go. Something a bit more permanent. Now you're still going to probably have to use freeze panes here so that way you can see each value close to each other.
A little bit more permanent than using the method from yesterday, the compare side-by-side.
You actually get those values on the same spreadsheet. Now if you needed to do some calculations like comparing delta's or something like that you could pull that off with this method. So probably in my opinion a better way to go, more permanent, gets more numbers on the same worksheet, lets you do calculations and you can even sort those together. A good topic for another day.
I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
