Excel - Merge Data from Multiple Sheets Based on Key Column

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 one

Channel: Learn Google Spreadsheets

Views: 171,890

Rating: 4.8616796 out of 5

Keywords: Excel, Merge, Data, Multiple, Sheets, worksheets, tabs, Key, Column, matching

Id: 325GKIXPsSI

Length: 9min 5sec (545 seconds)

Published: Mon May 20 2019