I have a background in SQL, and sometimes in Microsoft Excel i need to perform a simple inner join on two datasets. In this blog I explain how we can perform inner joins with Microsoft Excel relatively quickly.
Set up your data source
I am using two data sources which both shared a common column called ‘machine’.
One data source just contained the ‘machine’ column which i pasted into ‘Sheet 1’. And the other data source contained ‘machine’ and ‘description’ columns, which I pasted into ‘Sheet 2’. I then saved this spreadsheet as ‘data.xlsx’.
Import your data source
Create a new, blank spreadsheet. Click Data > Get Data > From Other Sources > From Microsoft Query.
In the ‘Databases’ tab, select ‘Excel Files’ and click Ok.
Select your spreadsheet called ‘data.xlsx’ and click Ok. If you see an error saying ‘This data source contains no visible tables’ just click ok, then click the ‘Options’ button, select ‘System Tables’ and click Ok. You’ll then see your sheets where you can select all the columns you require – I selected Sheet1.Machine and Sheet2.Description. Click Next.
You will then see a message stating ‘the query wizard can not continue because it can not join the tables in your query. You must join the tables manually in Microsoft Query by dragging the fields to join between the tables’. Click ok, and drag Sheet1.Machine onto Sheet2.Machine like so:
Now select File > Return Data to Microsoft Excel. Click Ok.
Done!