About the Merge Worksheets commands

Use the Merge Worksheets commands to combine two or more open worksheets into one new worksheet. Stored constants, matrices, DOE objects, and worksheet descriptions are not transferred into the merged worksheet.

There are two commands to merge worksheets:
  • Data > Merge Worksheets > Match Values: If you have variables that use the same set of units (for example, subjects or locations) in two different worksheets, use Match Values to combine the information into a single worksheet so that each row contains information for the same unit.
  • Data > Merge Worksheets > Side-by-Side: Merge multiple worksheets by placing all columns side-by-side in a new worksheet.

Using By Columns with Merge Worksheets: Match Values

If you have information for the same set of units (for example, cases, subjects, or locations) in two different worksheets, you can use the By Columns option of merge to combine the information into a single worksheet so that each row contains information for the same unit. When you specify By Columns that contain the unit information for each worksheet, Minitab matches the values in the By Columns from each worksheet and puts them in ascending order. The remaining columns are sorted by the By Columns.

Example

Suppose your company owns four plants, or facilities. One worksheet contains net profit information for each plant and a different worksheet contains expense information for each plant.

C1 C2
Plant Profit
3 23
1 24
2 29
4 30
Worksheet 1
C1 C2
Facility Expense
1 12
4 10
2 14
3 15
Worksheet 2
Worksheets before merging

The column Plant in Worksheet 1 and the column Facility in Worksheet 2 both refer to the same four locations. When you combine the worksheets, you want the profit and expense information for each location in the same row.

C1 C2 C3 C4
Plant Profit Facility Expense
1 24 1 12
2 29 2 14
3 23 3 15
4 30 4 10
Merged worksheet

The following worksheet was created by merging Worksheet 1 and Worksheet 2 using Plant and Facility as By Columns. In the merged worksheet, all data for each location are on the same row.

Guidelines for By Columns

  • The names of the By Columns for the worksheets can be the same or different.
  • The By Columns must be the same length. Other columns that are not the same length as the By Columns are excluded from the merged worksheet. A note in the Session window lists the columns that are excluded.
  • By Columns must have the same data type: numeric, date/time, or text.
  • With value ordered text, the value orders must be the same for both By Columns.

Using the Include multiple observations option with By Columns

You can specify how you want to handle multiple occurrences of a value in the By Columns. One or both By Columns may contain a value that is repeated within the column. By default, Minitab creates separate rows for each occurrence in the merged worksheet.

Examples

C1 C2
Plant Profit
2 23
1 24
2 29
4 30
Worksheet 1
C1 C2
Facility Expense
1 12
4 10
2 14
4 15
Worksheet 2
Worksheets before merging

The By Columns, Plant and Facility, both contain repeated values. Plant number 2 is represented twice in Worksheet 1. Facility number 4 is represented twice in Worksheet 2.

The following worksheets were created by merging Worksheet 1 and Worksheet 2, and selecting Include multiple obs for both worksheets, one worksheet, or neither worksheet:

C1 C2 C3 C4
Plant Profit Facility Expense
1 24 1 12
2 23 2 14
2 29 2 14
4 30 4 10
4 30 4 15
Include multiple observations for both worksheets

The merged worksheet includes both rows for Plant 2 from Worksheet 1. The worksheet also includes both rows for Facility 4 from Worksheet 2.

C1 C2 C3 C4
Plant Profit Facility Expense
1 24 1 12
2 23 2 14
4 30 4 10
4 30 4 15
Include multiple observations for Worksheet 2 only

The merged worksheet includes the repeated values from Worksheet 2 (4 in Facility). However, only the first row for Plant 2 is included from Worksheet 1.

C1 C2 C3 C4
Plant Profit Facility Expense
1 24 1 12
2 23 2 14
4 30 4 10
Do not include multiple observations for either worksheet

The merged worksheet does not include any repeated values from either worksheet.

Using the Include unmatched observations option with By Columns

You can specify how you want to handle observations in the By Column of one worksheet that do not have a match in the By Column of the other worksheet. By default, Minitab creates rows for unmatched observations in the merged worksheet and displays missing value symbols where no match exists.

Examples

C1 C2
Plant Profit
1 23
2 24
3 29
4 30
5 31
Worksheet 1
C1 C2
Facility Expense
1 12
2 10
3 14
4 15
6 17
Worksheet 2
Worksheets before merging

The By Columns, Plant and Facility, both contain unmatched values. Plant contains the value 5, which has no match in Facility. Facility contains the value 6, which has no match in Plant.

The following worksheets were created by merging Worksheet 1 and Worksheet 2, and selecting Include unmatched observations for both worksheets, one worksheet, or neither worksheet:

C1 C2 C3 C4
Plant Profit Facility Expense
1 23 1 12
2 24 2 10
3 29 3 14
4 30 4 15
5 31 * *
* * 6 17
Include unmatched observations for both worksheets

The merged worksheet includes the unmatched observations from both worksheets (5 in Plant and 6 in Facility). Missing value symbols (*) fill the resulting empty cells.

C1 C2 C3 C4
Plant Profit Facility Expense
1 23 1 12
2 24 2 10
3 29 3 14
4 30 4 15
* * 6 17
Include unmatched observations for Worksheet 2 only

The merged worksheet contains the unmatched observation from Worksheet 2 (6 in Facility). The merged worksheet does not include the unmatched observation from Worksheet 1 (5 in Plant).

C1 C2 C3 C4
Plant Profit Facility Expense
1 23 1 10
2 24 2 12
3 29 3 14
4 30 4 15
Do not include unmatched observations for either worksheet

The worksheet does not include any unmatched observations.

Using the Include missing as a By level option with By Columns

By default, rows with missing values in either By columns are omitted in the merged worksheet. You can include missing values within By columns for both worksheets. The missing observations are then treated as distinct values. Missing By column rows in one worksheet are matched with missing By column rows in the other worksheet.

By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy