Compare and merge Microsoft® Excel® worksheets, names and VBA Projects
Compare spreadsheets with table/database structure
Compare spreadsheets with table/database structure.
Very often you need to compare data with "Table" or "Database" structure. In this case each column(row) has a heading, which identifies this column or row. Usually price lists, different reports, etc. has this structure, so the options described below should be applied.
This is example of the spreadsheet with database structure:
First row contains names of fields and first column contains list of primary keys.
For this case xlCompare offers you 2 features which makes comparison results more accurate:
First cell in column contains Primary Key or Field Name if this option is checked - column is compared by first non-empty cell. If values of first cells are different - columns are treat as not equal. It helps xlCompare to find added, deleted and data quickly and more exactly.
First cell in row contains Primary Key or Field Name - same option, applied to rows.
If option is checked - data is sorted before comparison. If your worksheets has same records but in different order this allows to detect changes more exactly, because row/column order is not important in this case.
In the above example both options should be checked. This helps to get more accurate comparison results.
Please note, Primary Key column/row should not contain duplicated values. It may contain empty cells, but it should not contain duplicated non-empty cells. Otherwise this column/row will not be recognized as Primary Key. If xlCompare is unable to detect Primary Key column/row, appropriate Database Comparison option is ignored.