In your day-to-day work, many of you copy data from a company’s business information system or some spreadsheet where they are exported to create the necessary reports. The problem arises when the data changes. How to trace where the change occurred? What is the difference between a source and a derived table? In the text that follows, you will see how to use the INDEX and MATCH functions, as well as the conditional formatting technique, to highlight the differences between the tables.
As an example, we took the original table of a travel agency, which contains (fictitious) data on clients, arrangements, prices, payment amounts and differences …
Based on the original table, a derived table was created by copying, which includes all clients except those from Serbia. It contains only basic customer data and payment records.
Since the copied table is only a “picture” of the state of the original table at the time of copying if there is a change (e.g. if the client pays an additional amount of the price of the travel arrangement) we will not be able to notice the difference. How to compare the PAYMENT (UPLATA) column in the source and derived table?
Let’s add another column and call it MDU. We will write the following formula in it:
=INDEX(MD;MATCH($A2;MD[IME I PREZIME];0);MATCH($D$1;MD[#Headers];0))
The formula instructs Excel to find the value in the intersection of the client’s name and the PAYMENT (UPLATA) column in the MD table (source table). In this way we come to the value of the UPLATA column in the source table. A short note: in this example, we used the client’s name, and in practice it may not be such a smart move. It is better to use a personal identification number or some other information that uniquely identifies the client.
To emphasize the difference, it is best to apply conditional formatting using a formula. After starting the option, we will enter the formula:
and if the condition is met, we should paint the background of the cell to orange.
Note that we have fixed the column in the addresses of the cells being compared. In this way, we will “paint the entire row“, i.e. clients with a data difference in the original and derived table. Finally, if we hide the MDU column we have an accurate report with emphasized differences.