Combine data from worksheets into a new worksheet.

Beginning with two worksheets containing fictitious data where both share a column – you need at least one “key” between the two, I will use Excel to combine both sheet’s data into one worksheet.

First, save the spreadsheet somewhere easily accessible.

Define Dataset Ranges

In Excel, name each range of data you will later address when building the query.
Select the entire range of data, then define a name.

Repeat for the second worksheet.
You should now have both datasets defined (Name Manager).

Build a Query

Now you will build a query to combine data into one sheet.
Data -> Get External Data -> From Other Sources -> From Microsoft Query.

Databases: Excel Files*, OK.

List Files of Type: All, then select your spreadsheet above (DataMerge.xlsx), OK.

Each name you defined will be displayed, select one name, then ‘>’ to move all its fields to the left.

For the other Sheet (Sheet2), just select the additional fields, Next.

Ignore warning, OK.

You will now see your initial, duplicated dataset from the combination of both sheets.

Drag your key field (Name) from one sheet (Sheet1) to the next (Sheet2) and a connection (relationship) will appear (solid line).

You should now see a filtered (joined) unique list of values from both sheets.

Click the icon to Return Data.

When the Import Data window appears, place your cursor in the upper-left cell of the sheet where you want the data, ensure the following selections, OK.

You will now see the results – unique data from both sheets combined into one sheet.