Table of Contents
KEEP/REMOVE MATCHING
Category: Transform / Filters
Description
This action keeps or removes rows where values in the specified column are matching the specified column of another table.
Use cases
- Finding the difference between two lists, or the overlapping records of two lists.
- Filtering a table to keep only records with a subset of values in a particular column.
- Finding new records that haven't been exported yet.
Action settings
| Setting | Description |
|---|---|
| Mode | Select whether values in the current dataset are kept or removed if they match values in the lookup table. Options: Keep matching (remove mismatching) or Remove matching (keep mismatching). |
| Lookup table | Select the dataset containing the values to compare to. |
| Matching columns | Select the columns in the current table and the lookup table to match values in. This option appears once the Lookup table has been selected. |
Remarks
This action is case-sensitive and will only match values with the same case.
Values must be identical (equal) in order to be matched. If another matching mode is needed (e.g. "Starts with") use the Match action first to do the matching.
The action's icon will change depending on the mode selected (keep/remove).
Examples
Example #1
Keep the rivers in Table 1 that match the Continents listed in Table 2.
Before (source table)
Table 1: The longest rivers in the world
| River | Length (km) | Continent |
|---|---|---|
| Nile | 6650 | Africa |
| Amazon | 6400 | South America |
| Yangtze | 6300 | Asia |
| Mississippi | 6275 | North America |
| Yenisei | 5539 | Asia |
Table 2: Continent list
| Continent name |
|---|
| Africa |
| South America |
After (result table)
| River | Length (km) | Continent |
|---|---|---|
| Nile | 6650 | Africa |
| Amazon | 6400 | South America |
Action parameters
Mode: Keep matching
Matching values in table: Table 2
Column of the first table: Country
Column of the second table: Country name
Example #2
Remove the European countries from the first table.
Before (source table)
Table 1: Average income and deductions.
| Country | Gross income | Disposable income | Compulsory deductions |
|---|---|---|---|
| United States | 56067 | 45582 | 18.7% |
| Ireland | 51218 | 48073 | 6.14% |
| Luxembourg | 55176 | 47716 | 13.52% |
| Australia | 51050 | 42617 | 16.52% |
| Switzerland | 53716 | 48414 | 9.87% |
| Canada | 45896 | 37469 | 18.36% |
Table 2: Some European countries
| Country name |
|---|
| Ireland |
| Luxembourg |
| Switzerland |
After (result table)
| Country | Gross income | Disposable income | Compulsory deductions |
|---|---|---|---|
| United States | 56067 | 45582 | 18.7% |
| Australia | 51050 | 42617 | 16.52% |
| Canada | 45896 | 37469 | 18.36% |
Action parameters
Mode: Remove matching
Matching values in table: Table 2
Column of the first table: Country
Column of the second table: Country name
Community examples
- Compare two Excel sheets with EasyMorph (Project; Module: Main; Group: Compare data; Table: Mismatching rows;
Action position: 3) - “Printed” Text File: Could EasyMorph import this? (Project; Module: Parse Group; Group: Tab 1; Table: Table 1; Action position: 2)