Excel - Merge Data from Multiple Sheets Based on Key Column

Sharing buttons:

alright so in this video I'm gonna show

you how we can avoid doing any vlookups

and still be able to join data from

different tables in Excel so this is

gonna be irrelevant if you're in Excel

2016 or a higher version or if you're in

2010 or 2013 you can also do this by

adding an add-on power query to your

version so first of all let me just go

over what we're trying to accomplish

here so if I have this transactions data

tab and I have this products tab I have

connecting columns stock numbers here

and if I go to transactions I have stock

numbers here too now I want to use that

to bring over some data from products to

transactions so for example brand Coast

maybe size so if I was trying to do this

video lookup I would go to transactions

and do equals vlookup and then choose

the stock number that's connecting peace

comma go to products select my array

which would start from stock numbers and

up lock the range with f4 or however you

like locking the ranges comma and then

count one two three if I wanted to brand

three and then zero if I want this to be

an exact match I'd entered that brings

me the brand I double click I send this

down that works then I would have to

take this and pretty much just repeat

this all over again for the next one and

get the other column I could copy and

paste but again we would have to repeat

this we have the stock number I have to

go under products again select this and

then we'll locked range we'll select

let's say now Coast so 1 2 3 4 5 and

then comma 0 for exact match and then

I'd have to repeat this for every other

column I want it from products and keep

going until I'm done and then let's say

I also want some information from

salespeople tape

so then we're connecting with wrap IDs

and we have wrap IDs here so I would

have to do another vlookup to go grab

what's matching that ID which for the

first line would be ID 45 so that's this

one and then maybe I get the first and

last names or maybe I want them to

combine right something like that so

that's pretty much what I'm trying to

accomplish but I don't want to do all of

these vlookups

so I'm gonna delete this so you could do

this directly on this worksheet but the

easiest way to do this is to just leave

this file alone and then do all of your

final spreadsheets in a new worksheet so

I'm gonna create a new file so that's

ctrl n that's gonna create a new file

for me there we go or you can go file

new the same thing so in this new file

I'm gonna go on their data tab and in my

data tab

see there's this get and transform

section right here I'm gonna go under

new and here from file from workbook and

here I'm gonna select that workbook so

that's the one I'm gonna select that one

hit import so that's gonna open this

it's basically pulling all the tabs from

that other workbook now we have to

select which ones we want so I'm gonna

click select multiple items

I'm gonna grab transactions tab I'm

gonna grab products and salespeople so

once I have all of these I'm gonna go

ahead and click edit so now we have all

of that in here in our power query so

you can see this is my transactions

table if I click on a lab see products

this is my products table now this is my

salespeople table now if you're doing

this you want to make sure that in this

tables let's say I'm pulling some

information from products right I would

have make sure that I don't have any

repeating IDs

here in this lists so you want to make

sure these are unique and you could

actually just remove those duplicates

right here if it's necessary but I'm not

really sure why you would want to do

that so you may want to put some thought

into this but for now I could just click

on remove so I have like what 24 lines

here so if I do remove duplicates see it

stays the same 24 so I didn't really

have any duplicates so I'm fine I'm

gonna actually undo that step here and

right so that's that so if I go to

salespeople the same thing applies here

we don't want to have two people with

the same ID for the wrap so once I have

all of these three now I want to make

what I was talking about which is all of

those merged together so what I do I go

to transactions and then do this merge

right here see so I'm gonna click merge

this is gonna show up now it's gonna ask

me which table I want to merge so I'm

gonna start with products so the common

thing was stock number stock number here

stock number here good

I have those selected I'm gonna select

those and heat okay so that gives us

this table thing on the right so we

don't really want this we want the

columns right so I'm gonna click on this

little icon to expand this and here I'm

gonna select which columns I want so I'm

gonna uncheck all of this I'm gonna

select size Coast and brand heat okay

and here we go now I have all of those

matched and brought over here to this

tab now if you want this sorted by

transaction ID the way it was sorted on

our spreadsheet you could also go here

and make sure this sorted ascending now

the next step I wanted to do I wanted to

make sure I also add salespeople data

now from salespeople

I don't want first and last name I want

first and last names combined

so what I'm gonna do I'm gonna go here

and select the first name column press

control and select the last name column

now these two are selected so I'm gonna

go here transform another transform I'm

gonna go here and do merge columns so

this is gonna show up it's gonna ask me

what's the name of the new column I'm

gonna call this full name and it's gonna

ask me what do I want as a separator

between first and last names so I want a

space so I'm gonna go here space is good

select that it okay here we go now we

have our full name column so now I want

to bring those over to our transactions

so I go to transactions now to this one

I'm gonna merge again so I'm gonna go

back to home merge and this time I want

to merge salespeople table the common

column here wrap ID wrap ID hit OK again

it's gonna add this on the right I'm

gonna open that up and select which

columns I want so I'm gonna unselect all

of these I just want the full name good

enough hit OK all the names are here now

once I'm done with all this merges and

all this is ready I want to put this

back in Excel so to do that you just go

closing a load closing load and that's

just gonna load that to excel so here we

go so this was our empty tab it also

loaded this two other tables here

technically we don't really need them so

I'm gonna delete the ones I don't want

so this is my transactions table that's

what I want I don't want to look at this

one so I'm gonna right click and delete

that and then I don't want this one

either so I'm gonna right click and

delete that - so this is all now coming

so sheet 1 I don't need that - this is

all coming from that file that external

file that's not this far

we're pulling the data combining it

together merging it and then I put it

back into my spreadsheet and that should

do it for this video thanks for watching

please subscribe and I'll see you next