Select menu: Spread | Manipulate | Merge
Use this to merge the data from two spreadsheets. The rows are matched using the values of up to 4 pairs of specified columns. You can also specify what should be done with any rows that do not match any from the other spreadsheet. Columns from the second spreadsheet are added to the merged spreadsheet. Optionally, columns with the same name can be superimposed on matched rows, with the values in the first spreadsheet replaced by those from the second spreadsheet, or only replaced if the values are missing in the first spreadsheet, or not missing in the second spreadsheet. This operation can be undone wit the Edit | Undo menu if it does not turn out as expected. If multiple items with the same value exist in the two matched columns, the first row will be matched with the first row in the second spreadsheet, the second row with the second row etc. The resulting spreadsheet is sorted on the matching row values.
Merge data from sheet
The spreadsheet to merge data from, selected from a dropdown list of the other open spreadsheets.
Number of columns to match
A number between 1 and 6 which specifies how many pairs of columns are to be matched between the two spreadsheets. This number of columns must be selected for each spreadsheet. A column within one spreadsheet should only be selected once. Columns matched together can be selected from the following two drop down lists:
Matching column
The columns in the first spreadsheet whose values are to be used in matching rows in the second spreadsheet.
With column
The columns in the second spreadsheet whose values are to be used in matching rows in the first spreadsheet.
Select columns to transfer
This opens the Select Columns dialog that can be used to specify a subset of columns in the second spreadsheet to be merged into the current spreadsheet.
Extra rows from merged sheet
What to do with unmatched rows in the merged (second) spreadsheet.
- Do not transfer these rows – Keep the current rows in the first spreadsheet and do not add in any new rows.
- Add in extra rows only if matched – Add in an extra row from the second spreadsheet, if another row already contains this value, i.e. if there exist two rows with a value 102 in spreadsheet 2, and only 1 in spreadsheet 1, then the second 102 row would only be added if this option item was chosen.
- Transfer all extra rows – Add in all extra rows from the second spreadsheet.
Tolerance on numeric matches
Specifies a positive value to be used as a tolerance when comparing two numerical values. If two values differ by less or equal to than this tolerance, then they will be taken as a match. Note, the value of 0 only matches identical values, as occurred in 6th edition and earlier. Problems with apparently identical values not matching can occur when these values differ by the least significant digit, with this not being displayed, e.g. the two numbers 0.3333333333333333 and 0.3333333333333332 differ in the least significant digit, but when display to less than 16 decimal places will appear to be equal. This problem occurs most often in calculated values, as for example the calculations 1/3 and 400/600 can give different results in the least significant bit in their binary representation.
Update existing columns in sheet
If Update columns (by name) is selected, columns with the same name will be updated, with the values from the second spreadsheet being placed into columns in the first spreadsheet. Otherwise columns from the second spreadsheet will just be appended. The default of using matching column names can be modified if the Specify matches item is selected (see below).
Missing/empty cells only
Only replace missing values in the matched columns in first spreadsheet.
Insert non-missing values only
Do not replace values in the first spreadsheet with missing values in the second spreadsheet.
Specify matches
If this is selected, then pairs of columns to be matched should be specified using the Matches button.
Matches
This opens the Match Merged Columns dialog which can be used to specify that matches between columns in the two spreadsheets. For each column to be transferred, a column from the original spreadsheet in selected. If no column in the original spreadsheet is selected, then the merged column will be added in as a new column in the current spreadsheet.
Example
Two spreadsheets are merged with C1 matched on C3.
Sheet 1 | Sheet 2 | |||
C1 | C2 | C3 | C4 | |
101 | 1 | 101 | 11 | |
101 | 2 | 101 | 12 | |
102 | 3 | 102 | 13 | |
103 | 4 | 102 | 14 | |
103 | 5 | 103 | 15 | |
104 | 6 | 105 | 16 |
The resulting merged spreadsheet from the three different options for Extra rows from merged sheet are shown below.
Option 1: Do not transfer these rows
C1 | C2 | C4 |
101 | 1 | 11 |
101 | 2 | 12 |
102 | 3 | 13 |
103 | 4 | 15 |
103 | 5 | 15 |
104 | 6 | * |
Option 2: Add in extra rows only if matched
C1 | C2 | C4 |
101 | 1 | 11 |
101 | 2 | 12 |
102 | 3 | 13 |
102 | * | 14 |
103 | 4 | 15 |
103 | 5 | 15 |
104 | 6 | * |
Option 3: Transfer all rows
C1 | C2 | C4 |
101 | 1 | 11 |
101 | 2 | 12 |
102 | 3 | 13 |
102 | * | 14 |
103 | 4 | 15 |
103 | 5 | 15 |
104 | 6 | * |
105 | * | 16 |
See also
- Spreadsheet Add Data menu for adding columns for other data sources.
- Append Data to Spreadsheet
- Reshape Data menu
- Match Merged Columns
- Unstack Spreadsheet
The JOIN procedure can be used in the command language to merge two data sets.