This menu allows you 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 with 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 drop down list of the
other open spreadsheets.
Number of Columns to Match
A number between 1 and 4 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 From Worksheet 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.
Action Buttons
| OK | Merge the spreadsheets and close the dialog. |
| Cancel | Close the dialog. |
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: 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: 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: 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
The JOIN procedure can be used in the command language to
merge two data sets.