Merge Spreadsheet

Select menu: Spread | Manipulate | Merge

Use this dialog to merge the data from two spreadsheets.

After you have imported your data, from the menu select Spread | Manipulate | Merge.
Fill out the fields as required then click OK.

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 action can be undone using 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 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 Matching column and With column dropdown 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