Data Merge

Overview

Data Merge is a tool to combine two tables by a matching column. This tool can be useful when different pieces of your interested data are dispersed across different tables. There are five different merge types that you can choose from our tool.

• Inner: use intersection of keys from both tables, you can see the example in the below picture.

  

• Left: use only keys from left table, you can see the example in the below picture.

  

• Right: use only keys from right tables, you can see the example in the below picture.

  

• Outer: use union of keys from both tables, you can see the example in the below picture.

  

• Cross: creates the Cartesian product from both tables, you can see the example in the below picture.

  

Videos

Examples

Here is a step-by-step example of using Data Merge to merge the data tables shown below.

1. Prepare an Excel sheet with data tables to merge.

   

2. Open Finplicity, and then click Data Merge. The form of Data Merge will appear, as shown below.

   

3. In Step 1, select the left data table location. Note that each table must have a header. In this example, select the range A2:D30 for the left table.

4. Select the right data table location. Note that each table must have a header. In this example, select the range F2:G30 for the left table.

5. Select the merge type from the dropdown options. In this example, select “inner” for the merge type.

6. Select the range for the output location. In this example, select J2. Now you can click “Next” to continue.

   

7. In Step 2, you can choose the Advanced Settings for your table merge. If you want to merge the tables based on the common column, select “Merge on common columns” radio button and choose the common column name from the “On” dropdown. If you want to merge the tables based on separated column names from left and right tables, select “Merge on different columns” radio button. Then choose left table column name from “Table 1 (‘left’) on” dropdown and choose right table column name from “Table 2 (‘right’) on” dropdown. Note if you leave the dropdown as blank, this will be the intersection of the columns in both tables. In this example, we will select “Merge on common columns” and choose “Date(MM/DD/YYYY)” column from “on” dropdown.

8. If you want to sort the join column name lexicographically in the result table, select “True” from “sort” dropdown. Otherwise, leave it as default. In this example, we will leave it as “False.”

   

9. Now you have finished all the steps in this form, and you can click “Run” to merge data or click “Save” to save this task. Below is the result.