Excel macro to compare two data sets.

The requirement is to be able to compare 2 data sets to see which rows are/are not in the other data set and where the same record exists whether the values in the non-primary key columns match. (A typical scenario would be the extract of data from two different databases which have been cut and paste into the xls..) The logical steps are as follows:

- Sort the two data sets according to the specified primary key column(s).
- Once sorted the records should be arranged such that they align in terms of matching primary key column(s). Where there is no match the rows should be offset with a null record in the other data set.
- Add a 'difference' column in the middle of the two data sets. This should say:
-'No match' where there is no matching record in the other dat set.
-'Same' where all the specified attribute columns agree.
-'Diff' where one or more of the attribute columns disagrees. Those attribute values that disagree should be - highlighted in colour.

Note.

- There may be one or more primary columns in each data set but there should be the same number in both for any given comparison.
- There may be different numbers of attribute columns in each data set.
- The macro should be parameter driven and should include:
- Which row the data starts on.
- Which primary columns should be used for the sort and record match.
- Which attribute columns should be compared.

Attached is a spreadsheet with two examples (input and output). One first has a single primary key and the second has 2 primary key columns. The first has two attribute columns to compare and the second has one attribute column to compare and an extra column in data set 1 which has no equivalent in data set 2.

The first tab contains the details about where the data is and which columns need to be sorted and compared. The macro should read these values on start up. The layout is suggested but please feel free to suggest any alternative. The key is flexibility to specify many columns. The examples in the xls are very simple.