Problem description & analysis

Excel has two rowwise forms of same structure, where column A is the logical primary key. The lower form(we called form2) contains new data and the upper(we called form1) contains old data, as shown:

Now we want to compare the difference between the two forms:

(1) Find differences between form2 and form1, store all newly-added data, as shown below:

(2) Find all the deleted data, as shown below:

(3) Fine all the modified data (primary key values remain the same while values of other columns are changed), as shown below:

Solution & explanation

Use the SPL XLL plug-in of Excel

Write the following formulars in blank cell.

（1）Find differences between form2 and form1, store all newly-added data.

=spl("=[E@b(?2),E@b(?1)].merge@od(_1)",A1:B5,A9:B13)

As shown:

（2）Find all the deleted data.

=spl("=[E@b(?1),E@b(?2)].merge@od(_1)",A1:B5,A9:B13)

As shown:

（3）Fine all the modified data (primary key values remain the same while values of other columns are changed).

=spl("=[[E@b(?2),E@b(?1)].merge@od(),[E@b(?2),E@b(?1)].merge@od(_1)].merge@od()",A1:B5,A9:B13)

As shown:

Explanation:

merge() function performs merge operation, where @o option is used to disable sorting on memory data and @d option enables getting differences during merge.

Perform difference between form2 and form1 by comparing their first columns to get the newly-added data.

Perform difference between form1 and form2 by comparing their first columns to get the deleted data.

Compare the whole rows in two tables to get the newly-added or modified data, which is then compared with step(1)’s result by rows to get the modified data.

Q & A Collection

https://stackoverflow.com/questions/63841318/comparing-column-a-to-b-and-b-to-a-and-copy-entire-row-of-missing-and-added-to-n