The List Comparison tools are designed to compare a primary list (the MASTER) against a secondary list (the SLAVE) for completeness and changes to key values. For access to these functions see the List Comparison Menu and the Audit Tools Shortcuts.

These functions rely on

• both lists must be within the active workbook - copy the lists into a single workbook if they exist separately
  
• the presence of a key field common to both lists - a field value that identifies the record uniquely
  
• the existence of field headers - it is helpful if the headers identify the fields descriptively and uniquely
  

There are three main types of comparison

• missing records - this reviews all the records in a MASTER list and verifies their existence in a SLAVE list. The missing records and their associated key values are listed out.
  
• changed values - this compares the primary values in the matching records from the MASTER and SLAVE lists. Those records where the values have changed are listed out.
  
• matching records - this lists out all the matching records between the MASTER and SLAVE lists. It reports the primary values in each list irrespective of whether they have changed.
  

Processes common to all three comparison functions

In order to make a comparison you must identify both the MASTER and the SLAVE lists. This means

• selecting the worksheet that contains the list
  
• identifying the list range - any worksheet can hold several data ranges any of which could be the list you want to compare. The process of selecting the worksheet also prompts the system to identify the first list that conforms to the expected criteria for lists. You must confirm that this is the right one.
  
• modifying the list range. The system automatically knows the total range of cells used in the worksheet. In order to select a new list range you must use the Column letter and Row number values to select any cell within the new list range. Clicking Re-evaluate will identify the new list for comparison
  
• once you have accepted the list range the system will post the field headings into the Key Field combo box. You must select the key field and validate that it is key.
  
• finally, you must identify the primary value that you want to report
  

The form itself identifes each of the processes you have completed by disabling the buttons for those processes. The form below shows the processes for the MASTER list complete (all the buttons disabled) but the identity of the slave list has not been started (the select button still enabled).

Remarks

You are not permitted to use a formula to create a key field (e.g. by concetenating the text from two fields together). The Create Key Field function overcomes this restriction.

See also

User Tools