Merge Sheets

The Merge Sheets command allows you to combine rows from the current worksheet with rows from another worksheet based on one or more matching group columns. The result is a new worksheets containing rows made up of cells from both worksheets.

Each worksheet has to contain one or more common columns that can be used to match rows between worksheets. These are called the group columns and are selected in the Merge Rows Based On These Groups section.

In the Merge These Columns and the With These From Sheet2 sections, select the desired columns for the output worksheet.

Rows in Sheet1 that match a row in Sheet2

When checked, group column values that appear in both Sheet1 and Sheet2 are included in the output worksheet.

Rows in Sheet1 that don't match a row in Sheet2

When checked, group column values that appear in Sheet1 but are missing in Sheet2 are included in the output worksheet with the missing values from Sheet2 blanked out.

Rows in Sheet2 that match a row in Sheet1

When checked, group column values that appear Sheet2 and Sheet1 are included in the output worksheet.

Rows in Sheet2 that don't match a row in Sheet1

When checked, group column values that appear in Sheet2 but are missing in Sheet1 are included in the output worksheet with the missing values from Sheet1 blanked out.

Use Levenshtein Distance Fuzzy Matching

The Levenshtein distance measures the difference between two text values in terms of the number of keystrokes it would require to turn the first text value into the second text value.

Before computing this difference, all puctuation characters and spaces are removed from both values.

The lower the difference, the more similar the two values are. Identical values (exact matches) have a difference of 0.

For example, the distance between "123 Main Street" and "123 Main St" is 4. In this case all it takes is 4 taps on the backspace key or delete key to transform the first value into the second value.

Here are a some more examples:

  • The distance between "123 Main St" and "123-1 Main St" is 2 since only 2 characters need to be inserted.
  • The distance between "153 Main St" and "123 Main St" is 1 since only 1 character needs to be overtyped.
  • The distance between "153 Main St" and "153 Main St" is of course 0 since the values are the same.
  • The distance between "John Smith" and "John M. Smith" is 2 since the letter M and a dot need to be inserted.

The following parameters determine what to treat as a possible match.

Minimum Distance

This is the minimum difference. If you want include exact matches, set this to 0, otherwise set it to 1 or higher. The lower this value is, the more potential there is for matches and false positives.

Maximum Distance

This is the maximum allowable difference. Items whose difference is greater than this value will not be considered as possible matches. The higher this value, the more potential there is for matches and false positives.

Maximum Matches

Since this process involves comparing every group value in Sheet1 with every group value in Sheet2, this value is used to limit the number of possible matches.

Example

  • Open Easy-XL's Sample Workbook.
  • Select the Invoices Sheet.
  • Select Merge Sheets from the Easy-XL Menu.
  • In the Select a Sheet to Merge dialog box, choose the SalesPeople sheet.
  • In the Merge Rows Based on these Groups section, select Salesperson.
  • In the Merge These Columns section, select the columns you want to include in the output worksheet.
  • In the With These From Sheet2 section, select Name.
  • Click Finished.