How To Merge Two Excel SpreadSheets Using Vlookup – the video shows to easily combine the data from two spreadsheets into one as long as one column in each of the spreadsheets has a column with common data or a primary key.
How To Merge Two Excel SpreadSheets Using Vlookup
I Get Paid To Write Posts Like This – Discover How You Can Do The Same
|
| Thank you for visiting my blog. I get paid to write about anything including telling people about new products and also reviewing products. If you would like to know how to do this then Click Here Some people that do this and are making $5000 a month, some are making $10,000 part time and full time. There are even some people making over $100,000 a month with a blog like this. If you would like to join my team then Click Here nowWhen you click the link you will be taken to page where you enter your email address and then you will see a FREE VIDEO that explains how you can do it too. The video that explains how to do it is FREE so Click Here |
|
|
Excel Merge Two Spreadsheets Using Vlookup – VIDEO TRANSCRIPT BELOW
Hello it’s Neil Ball, your online marketing coach from www.workwithneilball.com.
In this training, I am going to demonstrate how to merge two spreadsheets together. This example is going to use two spreadsheets in the same worksheet. The easiest way you have got two separate books with the spreadsheets in is probably to put them in the same spreadsheet, or the same workbook.
Now once you have got the two spreadsheets in the same workbook like I have here, so I have got sheet one which has got the names, address, cities, states, zip for 29 people.
And sheet two has got the names and the date of births. And that is actually for 100 people. So obviously, there is more data in sheet two than there is in sheet one. So to do this, the process of merging, it actually uses the VLookUp function within excel. Now, I found that the explanation within excel of how to use this function is not very good. So a video is an excellent way of demonstrating how to use it, and then once you see it, you see it is a very easy thing to do.
So, the first thing I would recommend you do, is to take the range of data that you are wanting to use. That you want to merge. So, I want to take data from here, so there is a match and put it in the spreadsheet, so what I am going to have here is, every field that has a name that matches will also have the address details populated over here, for example the name column is common to both spreadsheets and will actually be used to tie the data across to each other. Very important, you have got to have data that has got some commonality so, the best way to do this is to highlight all of the cells where the data is that you are going to compare and copy.
So, I have highlighted all those there. So if you then go up to formula on the top tab here, and click define name. You can actually give that a name, so I am going to call it NamesAndAddresses. When you do this, I think it doesn’t like you having spaces in here, so you need to keep everything in a format similar to that, then click ok.
The next thing that you need to do is go into sheet one, and copy these fields here. Fields that you are actually going to want to copy across. So, name is already in sheet two, so I don’t need that. So I am just going to copy across these headers here, for address, city state and zip. So I can go copy, and drop them into here with paste. Now it is time to bring the data across. Now the easiest way to do this as I said, is use the VLookUp.
So then click here, and then click, equals. You will then see here that this box changes. I am going to tell you how to find the VLookUp just in case you do not know how. One easy way of doing it, is to go to more functions like that and then type in VLookUp.
Like that, and then click go. You will then see at the top of this list is VLookUp.
So if you double click that. Then, it brings up this box, to make this work, what you need to do is complete these boxes. You will see if you read this information here, it is not very clear as to what it is actually saying, or what you need to do. So the first thing that you need to do is to define what it is when excel is looking at this particular row it is going to be looking for in the other spreadsheet. Or in sheet one, so, in this case it is going to be that which is A2. So, that is the first thing. The next thing you need to think about is, when this is copied, to the next columns, you are always going to want to use column A for your point of reference, so to make sure that it always uses column A for the point of reference, you need to put a string sign there, so $A. Now the next thing, Table array now table array was what was highlighted when I showed you all of this, and given the name that we did before. So I will just get rid of that there and go back to here.
Like that, and to bring up the list of names that you have created, press F3, you will see that it brings up the paste name in here, so if you click Names And Addresses which is what I named it, and click ok, it will then populate that here like that. This column here, the column index number, is the data column that is going to populate this particular cell from sheet one.
So, and the way this works, it numbers these, this is column one. The name column here is column one. The address column two. Column three is the city column. Column four is the state and column five is zip. So, as I said before, as I have already got name in here, on sheet two we do not need to copy name across again. So the first thing we need to do is copy this column two. So here, you can just put 2.
It has actually just made an error there because it was still on there, but if I just get rid of that on the end I should be alright. Now, the next thing you need to look at, is Range lookup. Now again, this is a bit of a confusing explanation of what this actually means. Obviously written by a programmer.other than somebody who reads English. What it is actually saying is that if you want an exact match, then put FALSE here. If you are wanting to do this, then that is what you are wanting to do.
So, FALSE, is what you want to put in there, and then click ok. Now, it has come up with N/A. What that is actually saying is that, that particular name does not match any of the names in this list here. So, what we need to do now is, copy that down all of here. So if you just position your mouse over this right hand corner of the box and double click it. It will actually copy that all the way down for every column, sorry for every row that is populated here.
So as you can see, what it has done is, it has identified all of the data where there is a match, so this here, is obviously a match. To there, and it has then copied the first part of the address to this box here. Which is what we said we wanted, we wanted it to take column two. And put that here. So, that is all ok. So, the next thing, which is a little bit annoying is these N/A’s here, they really are annoying.
Now, you can get rid of that, by using the function, which is built into excel. 2007 and also excel 2010. I am not sure if it is in 2003 but it is in those two.
It is called the IfError function. Now, just to demonstrate this for you first, if I just put equals here and then, I can show you where it is on here. You can actually find it by putting IfError like that. And you will find it and you will see returns. Well half the time these are a little bit confusing, but this is the function that you use for this. Basically what you are saying is, if there is an error, what does it do? So you need to tell it what to do when there is an error.
So, if you click ok, then, the first part is the value. So the value is, when I actually set this up properly, is going to be the VLookUp function. That we have set up for this column here. And, then what you need to say is, you need to say if there is an error, what is it that you want to display. Now in this particular example, we want it to display a blank cell. So, to do that if you put a double speech SPACE double speech, like that and then click ok. That is how you do it, so, for the sake of argument, I am going to write Test there. It allows you to see how it actually structures this. So, what we need to do is put this here, as you can see in the box up here. Where it says IFERROR( Test,”” ).
What we need to do, is use that formula there, where it says Test, we need to put the VLookUp information. That is in column A2 here. So, I will show you how to do that now. So, I am going to leave that there just in case. But, if I come across here, the way that you can do that is by placing your cursor just after the equals there. And now you know the format of it, you can actually write Iferror like that. And then the open brackets then VLOOKUP is replacing where there was a test, you are going to put where test was in the example that I have just showed you. Then comma, then double speech marks, then SPACE then double speech marks, close brackets.
And now press enter, you will see the error has now disappeared. So, we now copy that down the column by double clicking the bottom right. It is looking a lot neater now because all of the errors have gone out of it. The next thing is, how do we get the city here. That is very straight forward, all you need to do is copy this cell to here.
And amend it slightly. So, we have copied it to there, the only thing that this needs is column two is not what we want in here. We actually want column three. So, that needs to be changed. So coming back to here, edit that manually. Just change the two to a three. And again that can now be copied all the way down and you can see that it is now populating. And the same again, copy to the next one, like that, I am just dragging that copy it. And once that comes in there, we now want state, which is column four. So change number three here, to number four.
And then copy it down there, like that. And the next column I assume was going to be column five. Indeed it is. So there is column five. So now, come back to here, and copy this cell to here, like that, and then edit the four, so it is now five.
Press return and then copy that all the way down, by double clicking the bottom right hand corner of this box, we have now got the zips all the way down, like that.
And that is the two spread sheets merged together.
Which once you actually understand it, it is a great feature, and it took me quite a while to work out how to do this. So, hopefully that has been helpful for you.
To get more tips, tricks and information, and also information on making money on line, you can join my mailing list by going to www.workwithneilball.com.
Again, that is www.workwithneilball.com.
Until next time, goodbye.
How To Merge Two Excel SpreadSheets