I’m trying to generate a report that will regularly compare current raw data with that of previous reporting period and only show rows (primary keys) where a certain column’s value has changed between the two, or where a row is present in one spreadsheet and not the other.
The raw data will be placed in two separate worksheets within the file, for example as follows:
(Note: The values may be in any order and are not sequential, and there will be other columns present that are irrelevant to the example/summary)
Worksheet 1 (old data):
| Primary key | value |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
| D | 4 |
| E | 5 |
Worksheet 2 (current data):
| Primary key | value |
|---|---|
| A | 1 |
| B | 100 |
| D | 4 |
| F | 6 |
| C | 3 |
I’m looking for a solution that will provide the below result in a seperate summary sheet without editing/adding to the data worksheets at all.
Intended result (in separate worksheet, but same file):
| Primary key | Old value | New value |
|---|---|---|
| B | 2 | 100 |
| E | 5 | Not Found |
| F | Not Found | 6 |
In the above example, the information pertaining to B is included as the value has changed. E and F are included as they are present in one sheet and not the other. A, C, and D are not present in the output as they are present in both and contain the same value for each of those keys.
Also worth noting: this output is not the final product. The keys/values in this output sheet will need to be referenceable for other calculations, etc. in yet more sheets.
