How do I use Microsoft Query in Excel?

Microsoft Query allows you to match data from one sheet in a Microsoft Office Excel 2010 spreadsheet to data in another sheet and create a new sheet that combines the data from both source sheets.

Preparation

Requirements:

1. Both sheets must have column headings.
2. Both sheets must be in the same workbook.
3. Both sheets must have named ranges for the data.
4. Both sheets must have the common column formatted the same way.

Named Ranges:

1. Highlight the columns that contain your data.
2. Go to the Formula tab.
3. In the Defined Names group, select Define Name.
4. Enter a name.
5. Repeat for sheet 2.
6. Save.

Formatting:

If you are matching on BU ID, then BU ID in both files must both be formatted as General. If BU ID is formatted as text, then save a copy as a CSV and import the CSV into a new sheet in the other workbook.

1. Go to File, Save As, Save As CSV (Comma Separated Values). Save and close the CSV.
2. Import the CSV into the other sheet.
   1. Open the correctly formatted workbook.
   2. Select the second sheet.
   3. Select the Data tab.
   4. In the Get External Data group, select From Text.  Find the CSV and import it into the second sheet.
   5. Save the workbook.

The text you are matching must be in the same case in both sheets.  If the case is not the same, use the formula to change case and put the data in a new column.

Begin the Query

1. Go to sheet 3 in your workbook (sheets 1 and 2 contain the source data).
2. Go to the Data tab.
3. In the Get External Data group, select From Other Sources.
4. Select From Microsoft Query.  Select Excel Files.  Select the file you are currently using.
5. In the Query Wizard, in Available tables and columns, select the named range on sheet 1 and use the arrow to move it to the Columns in your query box.  Repeat with the named range on sheet 2.  Press Next.
6. Microsoft Query will display a warning:

The Query Wizard cannot 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.  Press OK.

1. Click on the common heading name in the first box. Drag and drop it onto the matching heading name in the second box.  A small line will link the two headings.
2. Double click on the line.  Select the appropriate type of join
   1. Inner join (Option 1): The query will return only rows that appear in both sheets.  Any rows that only appear in sheet 1 or sheet 2 will not display on sheet 3.
   2. Outer join (Option 2 or 3): The query will return all rows for sheet 1 and matching entries for sheet 2 OR the query will return all rows for sheet 2 and matching entries for sheet 1.
3. Click Add.  Click Close.
4. Click the Return Data button. Save as a Table in the Existing Worksheet.  Sort the worksheet by an appropriate heading.
5. Save the file.