I have two worksheets in two different excel files They both contain a list of names, id numbers, and associated data. One is a master list that includes general demographic fields, and the other is a list that only includes name and id, and an address. This list was written down from the master list by another office
I want to use the second list to filter the first In addition i want the results to include other fields from the master worksheet alongside the address fields of the second worksheet I know how i could do this very easily with a database inner join, but i'm less clear on how to do this efficiently in excel. How does one join two worksheets in excel? Bonus points for showing how to do outer joins as well, and i would greatly prefer knowing how to do this without needing a macro.
For 2007+ use
From Other Sources >
From Microsoft Query .
Excel Fileand select your 1st excel
- choose columns
(if you don't see any list of columns, make sure to check
- go to
Connections> [choose the connection just created] >
You can now edit this
Command text as SQL. Not sure what syntax is suported, but i tried implicit joins, "inner join", "left join" and unions which all work. Here's a sample question
SELECT * FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b ON a.col2 = b.col2