How To Merge Two Excel SpreadSheets Into One

http://www.workwithneilball.com/ Click this link for more information and subscribe to my email list to get more free useful tips on internet marketing and making money online.

How To Merge Two Excel SpreadSheets Into One In Microsoft Excel
How To Combine Two Excel SpreadSheets Into One In Microsoft Excel
How To Combine Two Sets Of Data Into One SpreadSheet In Microsoft Excel

How To Merge 2 Excel SpreadSheets Into One In Microsoft Excel
How To Combine 2 Excel SpreadSheets Into One In Microsoft Excel
How To Combine 2 Sets Of Data Into One SpreadSheet In Microsoft Excel


Work With Neil Ball
http://www.youtube.com/user/WorkWithNeilBall

---

Closed Caption:

Hello its Neil Ball your online marketing
coach from WorkWithNeilBall.com. I am going
to demonstrate today how to take two spreadsheets
and combine the data from both of them into
a separate spreadsheet.This can be useful
if you have got two spreadsheets with two
lots of data that you want to combine together.
You have to be a little bit careful with this
because if there are duplicate values in one
of the spreadsheets that means the data on
each line isn't unique then the results that
you will get may not be exactly as you would
expect them.What I have done is, just to demonstrate
this, is I have made two spreadsheets, the
other thing about this just before I explain
what I have done, I have just remembered is
this process will only work in Microsoft Excel.
So what you need to do, is the data that you
need to combine, you need to use separate
sheets in the workbook to actually make this
happen. Just looking at this here, what I
have got is, I have got a spreadsheet which
I have created which has got name and zip
code. You have got 51 items here with name
and zip code. What my idea was with this is
that I wanted to find out city and state and
each of the zip codes that these were in and
sort them out so that all the data was combined
together.Now I have also got another spreadsheet
here which has zip, city and state, so for
this to work you have got to have a column
of data which will be in both sets of data
so in this case, zip is the same in each one,
so that is what is going to tie everything
together. One of the difficulties in using
zip codes as I have done here is that they
are not totally unique as I have found here
with this data set here which I have got.
I'm not quite sure why that is but the same
zip code can cover more than one area. And
because of that it means that this won't work
perfectly unless you make the data unique.
So what I'm actually going to do is, I'm going
to take this data and make it unique before
I start this process so, to do that I will
just go to data and go remove duplicates and
I'm just going to create a set of data which
is unique.The point of this process is not
necessarily to do this but to show that you
can have two sets of tables of data and you
can combine the two sets of tables together
to create one table which will have corresponding
values on the same row. So with this as I
showed you, I have got fifty rows of data
on here but on the zip code column I have
actually got 23,000 row of data so, it's a
nice demonstration as to how this can be quite
a useful tool.If you have got two sets of
data that you have to combine, you have got
a lot of data because it can save you quite
a bit of time and as long as you have got
some kind of uniqueness then with the fields
that are being used to tie the two sets of
data together then you will, then obviously
it is a huge timesaver. So the first thing
that you need to do is to highlight the data
in the first sheet and that you need to combine
right the way up to the top, you need the
top line here, and if you go to formulas,
and then go to define names, then what you
can do is, you can define a name for this,
so I am going to call this name and zip. Which
is actually the name of the sheet there. Now
you need to do the same on the second spreadsheet.
You need to go all the way down to the bottom
of the spreadsheet of the range of data that
you are trying to combine. And you need to
define again, I call this zip code table,
it's a lot easier if you call these what you
call the worksheet itself. Then you know exactly
where you are up to. So we have now defined
those as, use the defined name to define each
of those so now what you need to do is you
need to go to a separate tab where you want
to combine the data too. So I am just using
sheet three here. And you need to go to data
up at the top here. And you need to select
from other source and drop down to Microsoft
query, and click that, and then you need to
select Excel Files and click ok. And what
happens then is it will bring up this box
here, and then from here you need to select
where the data is going to come from.So in
my case here, it is on there, and there we
have it, it is on there. You need to select
the actual workbook and to do that, if you
select all files here, if you select that
you can select ok and this has got two sets
of data in here because I have done this previously,
so if I just take the ones I have created,
that was name and zip there. So add those
to there. So what is actually happening here
is
within that you have actually got, if I just
take this back across, you can see you have
got name and zip. You can either select the
container for these. So you can actually select
the individual columns of data. Whichever
is easiest or whichever one gives you the
results that you need. In this case, I want
to combine all the data so I just select the
container at the top. So I select that one
there, this is the other one that I just created,
so put that across there and then click next.
You will always get this error here or this
message here. Just click ok. It is just saying
it couldn't join the content together and
it is asking you to do it manually, and it
is asking you to click ok. And then what you
do is, the columns that you are going to tie
together is the zip code so if you just highlight
that and keep your finger on your mouse, and
literally drag it to there, and what it does
it, it will actually create a link between
the two columns that have the zip data, in
from the two sheets, so we will just give
it a moment to do that, right so it has done
that, and the next thing to do is, if you
go file, let me just pull this down a bit
so you can see it better. So on this Microsoft
query screen you can see now that zip is connected
to zip. The nextthing you need to do is if
you click file and then go to return data
and go to Microsoft Office Excel, like that.
Then you need to select the top left hand
sell location that you want the data to populate
from. Most of the time it is probably A1.
And you then click ok. In this box here, just
click ok and there is the data and what it
has done is, it has taken each of these, and
it has taken the zip code here and checked
it down these 23,000 items here and where
it has found a match, it has combined the
two sets of data together.So you can see that
that zip code matches that zip code because
they have come from, they are two different
sheets, so you have got the name, you have
got the city, you have got the date. And it
has done that for all 51 of them, absolutely
superb and it is a really useful thing to
do. I don't think the example I have give
you here isn't probably the best example of
how you would probably use this. Because as
I have said before the actually data and the
zip code file that I have got here is not,
well, I have looked at it and the zip codes
are not totally unique. This isn't a brilliant
example of how I would use this particular
feature but I am quite sure that you will
come across situations where you have got
two sets of data that you want to combine
together and you have one set of data in one
sheet and one in another and as long as you
have got uniqueness, and it works great of
the same number of records so if one set of
data is unique and you have got another set
of data that contains that unique content
that does extra content that you don't need.As
long as there is uniqueness, as it is showing
as long as there is a match, or anything that
doesn't match from the first table to the
second table doesn't actually get combined
into the, with Microsoft query to this final
results table that is being created.To subscribe
to my list and receive free tips on internet
marketing on how to make money on line go
to wwwWorkWithNeilBall.com, also for more
information on how I coach and how I teach
and the things that I do for my team, go
to www.WorkWithNeilBall.com.Until next time,
goodbye.