Excel Combine Worksheets

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.

1-Excel-Combine-Worksheets

2-Excel-Combine-Worksheets

3-Excel-Combine-Worksheets

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.

5-Excel-Combine-Worksheets 6-Excel-Combine-Worksheets

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

7-Excel-Combine-Worksheets

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.

8-Excel-Combine-Worksheets

9-Excel-Combine-Worksheets

Databases: Excel Files*, OK.

10-Excel-Combine-Worksheets

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

11-Excel-Combine-Worksheets

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

12-Excel-Combine-Worksheets

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

13-Excel-Combine-Worksheets

Ignore warning, OK.

14-Excel-Combine-Worksheets

You will now see your initial, duplicated dataset from the combination of both sheets.15-Excel-Combine-Worksheets

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

16-Excel-Combine-Worksheets

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

Click the icon to Return Data.

17-Excel-Combine-Worksheets

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.

18-Excel-Combine-Worksheets

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

19-Excel-Combine-Worksheets

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s