How to set up By Columns for Merge Worksheets: Match Values

Choose Data > Merge Worksheets > Match Values, select a worksheet to merge, then click By Columns.

In This Topic

Overview of By Columns
Guidelines for By Columns
Using the Include multiple obs option with By Columns
Using the Include unmatched obs option with By Columns
Using the Include missing as a By level option with By Columns

Overview of By Columns

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 merging worksheets 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.

Tip

If you want to stack corresponding columns from multiple worksheets, using Stack Worksheets might be easier.

Example

Suppose your company owns four plants, also called facilities. One worksheet contains net profit information for each plant. Another worksheet contains expense information for each plant. The column Plant in Worksheet 1 and the column Facility in Worksheet 2 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.

Worksheet 1

C1	C2
Plant	Profit
3	23
1	24
2	29
4	30

Worksheet 2

C1	C2
Facility	Expense
1	12
4	10
2	14
3	15

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.

Merged worksheet

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

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. Columns that are not the same length as the By Columns are excluded from the merged worksheet. A note lists the excluded columns.
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 obs 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 might contain a value that is repeated within the column. By default, Minitab creates separate rows for each occurrence in the merged worksheet.

Important

If you select Include multiple obs, then the merged worksheet might pair rows of data that you did not intend to interpret as single observations. If you deselect Include multiple obs, then you might exclude important data from the merged worksheet.

Examples

In the following worksheets, 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.

Worksheet 1

C1	C2
Plant	Profit
2	23
1	24
2	29
4	30

Worksheet 2

C1	C2
Facility	Expense
1	12
4	10
2	14
4	15

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.

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
2	29	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
4	30	4	15

Do not include multiple observations for either worksheet

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

C1	C2	C3	C4
Plant	Profit	Facility	Expense
1	24	1	12
2	23	2	14
4	30	4	10

Using the Include unmatched obs 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

In the following worksheets, 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.

Worksheet 1

C1	C2
Plant	Profit
1	23
2	24
3	29
4	30
5	31

Worksheet 2

C1	C2
Facility	Expense
1	12
2	10
3	14
4	15
6	17

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

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
5	31	*	*
*	*	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	12
2	24	2	10
3	29	3	14
4	30	4	15
*	*	6	17

Do not include unmatched observations for either worksheet

The worksheet does not include any unmatched observations.

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

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

By default, rows with missing values in either By Columns are omitted from 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.