Sometimes, when performing reconciliation of two tables, it is necessary to apply filters to columns with the same names in both tables simultaneously in order to select identical subset of data. For instance filter exactly the same Year, Region and Product in both tables. Having two identical sets of filters is not so convenient, especially if you need to filter by several columns and select various subsets of data in order to compare them.
Here is how you can filter data in two tables using only one set of filters:
 |
| (click to zoom) |
The idea is simple:
- Append both tables keeping track of data origin.
- Apply filters to the united dataset (filtered columns should have the same names in both datasets)
- Split the united dataset back into two tables. When filters for the united dataset change, the derived tables will update automatically.
This trick can work with more than two datasets, of course.
