Compare-IT for Microsoft Excel Help

To run Compare-IT, proceed to the EMAGENIT toolbar and click the Compare-IT button. In Excel 2007, proceed to the Add-Ins tab and click Compare-IT. The program will prompt you with two screens, follow the instructions. On the second screen which is the Information screen, leave the Compare-IT product ID box blank to run the program in demo mode. The program will allow you to click the "Compare Button" 20 times before prompting for a Compare-IT product ID which can be purchased from EMAGENIT.

Compare-IT Introduction

Compare-IT allows you to rapidly compare two worksheet tables and perform the following tasks:

1.  Search for missing or new records between two separate tables. Once found, the data can be extracted singularly, by record and/or can also be flagged.

2.  Find duplicate records within a table flagging the duplicate data and/or extracting the non-duplicate data to create a new table.

3.  Match table records and copy and paste data from one table to another.

4.  Do a comparison between two fields in separate tables and return a result of the comparison.

Compare-IT also allows you to compare table records based on one, two or three fields.

How to Open and Launch Compare-IT in Microsoft Excel

To open Compare-IT in Microsoft Excel, go to the Windows start button and click it, select the EMAGENIT grouping under programs, click on the Compare-IT icon and it will open in Microsoft Excel. If prompted, click the Enable Macros button to enable the program. Once open in Excel, proceed to the EMAGENIT toolbar in Excel 2003 and below and click the Compare-IT button. In Excel 2007, proceed to the Add-Ins tab and click the Compare-IT button.

How to Enable Macros in Microsoft Excel (Compare-IT Not Running)

If Compare-IT will not run in Microsoft Excel and prompts you as such, your macros have been disabled. To enable macros, proceed to Microsoft Excel Help and type in Enable Macros and follow the instructions about enabling macros. Note that you will have to shut down Excel and re-launch Compare-IT in most instances after adjusting these settings. Note that Compare-IT cannot override Microsoft Macro security so there is no way for our program to control this feature and enable it for you. If it could be done, we would have done it for you.

How to Purchase and Activate Compare-IT

Compare-IT automatically prompts you to purchase it after clicking the "Compare Button" 20 times by redisplaying its setup Information screen. You can purchase the software by clicking the purchase button on the screen then clicking the purchase button on the website page that is displayed. EMAGENIT will send you the product-ID within  3 hrs during our operating hrs Mon-Fri 8:00 AM-4:00 PM PST. You can also purchase it by clicking on the Purchase button to the left. To key in the product-ID, just launch Compare-IT and type the ID in the Information screen in the Product-ID box at the bottom.

Table Guidelines for Compare-IT  

A table must meet the following guidelines to be used by Compare-IT:

1.  It must have a header row.

2.  Its first field (i.e. column) must be continuous without blank cells. Compare-IT uses this column to count how many records (i.e. rows) the table has. If it does not meet this requirement, make a field and just place numbers in it starting at 1. Label the field Index.

3.  It must have at least a row or column separating it from other data on its worksheet. Compare-IT will work surgically on the table if it is surrounded by data but the best arrangement for a table is to be on a worksheet by itself.

4.  It must have no empty fields (i.e. columns) between fields.

5.  It must have at least two records (i.e. rows) including the header row.

6.  It can consist of a single field.

7.  Tables can be on separate worksheets, in separate workbooks or on the same sheet.

Source and Comparison Table Definitions

Source Table Definition

The source table is the new or suspect table that you want to compare against another baseline table. It can be a different configuration from the baseline table but be tied to it via a specific field or fields that contain like information like a record number or email.

Comparison Table Definition

The comparison table is the baseline table that you want to compare against. It contains data that you know to be valid. It can be a different configuration from the source table but be tied to it via a specific field or fields that contain like information like a record number or email.

General Compare-IT Operation

1.  The five option buttons in the Compare options section control what controls are enabled on Compare-IT when clicked. If a control is not enabled, try setting this section first before proceeding. These settings will configure the controls needed for the comparison task.

2.  The Enable output, Compare, Insert and Copy records boxes in the Output sections also control what Compare-IT controls are enabled. If a control is not enabled, uncheck all these controls then recheck as instructed in the instructions.

3.  To totally reset Compare-IT back to its initial state, close it by the (x) button at the top of its window then reopen.

4.  Note that if you are using tables from multiple workbooks, tile the workbooks in Excel for easy access.

5.  Note that you can manipulate Excel freely while Compare-IT is running. If you modify the tables Compare-IT is using or modify their worksheets, just simple re-designate the tables so Compare-IT knows about any changes.

6.  Note that Compare-IT does not distinguish between upper and lower case data.

7.  If you are trying to match data that has been directly downloaded from a database and are having trouble with the match, try cleaning and trimming the table first. You can use Case-IT to clean a table of any non-recognized characters that are sometimes downloaded from a database. These characters, while invisible, will throw off a match even though both values look like a match. You can also use Case-IT to trim a table of any unwanted spaces between words which can also throw off a match.

8.  In general, unprotect the worksheets that Compare-IT works with.

How Compare-IT Works

Compare-IT works by matching one, two or three  fields in a source table against the same in a comparison table. Once a match is found, Compare-IT can then determine whether the records containing the matching field values are new, missing or duplicated. Compare-IT can also transfer data between the two tables based upon the matching records. Compare-IT ignores blank cells and error value cells in the fields being compared. Compare-IT can be used on tables contained on the same worksheet, different worksheets or tables located in different workbooks. It can output results in a similar way.

Common Compare-IT Tasks And What Comparison Mode Should You Use

1.  You want to detect missing records in a source table. Use the Detect missing records mode. This mode can output field values from the missing records, flag them and also output the entire missing record (i.e. row). The missing records are extracted from the comparison table.

2.  You want to detect new records in a source table. Use the Detect new records mode. This mode can output field values from the new records, flag them and also output the entire record. It can also delete the new records if they are unwanted.

3.  You want to detect duplicate records in a source table. Use the Detect duplicate records mode. This mode can flag the duplicate records and also output the non-duplicate records.

How to Detect Missing Records in A Source Table

To use Compare-IT to detect missing records in a source table:

1.  Activate Compare-IT by clicking on the Compare-IT button on the EMAGENIT toolbar.

2.  Be sure the two tables being used by Compare-IT meet the minimum guidelines specified in the Table Guidelines section. If they exist in two separate workbooks, tile the workbooks in Excel for easy access. To tile a set of workbooks, consult Excel on-line help under tile workbooks.

3.  Determine the source table. The source table is the one you want to compare against another table. Once the source table has been determined, click on a cell within its boundaries and then click the Set source table box. The headers of the table should automatically be selected at this point. This shows that Compare-IT locked onto the complete table. If the headers do not select completely or not at all, uncheck the Auto Detect check box under the white box you just clicked in, select the entire header row of the source table then click in the Set source table box again. This will manually set the source table.

4.  Determine the comparison table. The comparison table is the one you will be comparing against. Once the comparison table has been determined, repeat the procedure discussed in #3 to select it only click the Set comparison table box.

5.  Click the Detect missing records... option button. Compare-IT in this mode will compare the designated source and comparison fields looking for records in the comparison table that are not found in the Source table.

6.  Select the source field in the Source field drop down that will be compared against a field in the comparison table. If you have repeating values in the 1st source field, select a secondary or even a third source field to compare by selecting their headers in the 2nd and 3rd source field drop downs.

7.  Select the comparison fields in the Comparison field drop downs below the Source drop downs. Be sure that if you are using two source fields you select two comparison fields.

Flagging Missing Records

8.  To flag the comparison records that are missing from the source table, check the Flag data check box. Missing records will be colored a light yellow. Note that this feature will operate in conjunction with the two features discussed next. Uncheck the Enable output check box since you are not outputting at this point.

9.  Click the Compare button to highlight the missing records in the comparison table that are not found in the source table.

Extracting Missing Record Data

8.  Compare-IT has the ability to extract data from the missing comparison table records that are detected. In addition to extracting the values from the fields being compared, Compare-IT gives you the ability to extract any field value. To designate the comparison field value to extract, select it in the Comparison extraction field drop down.

9.  Select an output cell to start placing the extracted comparison values at by clicking on the desired start cell in the desired workbook. Next click the Set output cell box to set the cell.

10.  To output the selected comparison field value, check the Enable output check box. If you want to include the cell address of the extracted field value, check the Include address check box.

11.  Click the Compare button to extract the data and place it at the starting output cell.

*Note that all cell values in the output area will be overwritten without warning.

Extracting Missing Records

8.  Follow steps 8 & 9 for extracting missing record data.

9.  To return the entire missing record from the comparison field, check the Copy entire record box.

10.  Click the Compare button to extract the entire record and place it at the starting output cell.

*Note that all cell values in the output area will be overwritten without warning.

How To Detect New Records In A Source Table

To use Compare-IT to find new records in a source table:

1.  Activate Compare-IT by clicking on the Compare-IT button on the EMAGENIT toolbar.

2.  Be sure the two tables being used by Compare-IT meet the minimum guidelines specified in the Table Guidelines section. If they exist in two separate workbooks, tile the workbooks in Excel for easy access. To tile a set of workbooks, consult Excel on-line help under tile workbooks.

3.  Determine the source table. The source table is the one you want to compare against another table. Once the source table has been determined, click on a cell within its boundaries and then click the Set source table box. The headers of the table should automatically be selected at this point. This shows that Compare-IT locked onto the complete table. If the headers do not select completely or not at all, uncheck the Auto Detect check box under the box you just clicked in, select the entire header row of the source table then click in the Set source table box again. This will manually set the source table.

4.  Determine the comparison table. The comparison table is the one you will be comparing against. Once the comparison table has been determined, repeat the procedure discussed in #3 to select it only click the Set comparison table box.

5.  Click the Detect new records... option button. Compare-IT in this mode will compare the designated source and comparison fields looking for new records in the source table that are not found in the comparison table.

6.  Select the source field in the Source field drop down that will be compared against a field in the comparison table. If you have repeating values in the 1st source field, select a secondary or even a third source field to compare by selecting their headers in the 2nd and 3rd source field drop downs.

7.  Select the comparison fields in the Comparison field drop downs below the Source drop downs. Be sure that if you are using two source fields you select two comparison fields.

Flagging New Records

8.  To flag the source records that are new, check the Flag data check box. New records will be colored a light yellow. Note that this feature will operate in conjunction with the two features discussed next. Uncheck the Enable output check box since you are not outputting at this point.

9.  Click the Compare button to highlight the new records in the source table that are not found in the comparison table.

Extracting New Record Data

8.  Compare-IT has the ability to extract data from the new source table records that are detected. In addition to extracting the values from the fields being compared, Compare-IT gives you the ability to extract any field value. To designate the source field value to extract, select its header in the Source extraction field drop down.

9.  Select an output cell to start placing the extracted source values at by clicking on the desired start cell in the desired workbook. Next click the Set output cell box to set the cell.

10.  To output the selected source field value, check the Enable output check box. If you want to include the cell address of the field value, check the Include address check box.

11.  Click the Compare button to extract the data and place it at the starting output cell. Note that all cell values in the output area will be overwritten without warning.

Extracting New Records

8.  Follow steps 8, 9 & 10 for extracting new record data.

9.  To return the entire new record from the source field, check the Copy entire record box.

10.  Click the Compare button to extract the entire record and place it at the starting output cell.

*Note that all cell values in the output area will be overwritten without warning.

Deleting New Records

8.  Uncheck the Enable output check box.

9.  Check the Delete records check box.

10.  Click the Compare button to delete the records not found in the comparison table. Note that the records cannot be restored once deleted. It is recommended that you first use the methodologies discussed above to first determine which records are new and should be deleted. Once that check is complete and you are satisfied, use the methodology just discussed to delete the records.

*Always use copies of your original data when performing a delete operation.

*Note that if you think the comparison table contains information not found in the source table and you wish both tables to be exactly equal, reverse the table designation in 3 & 4 making the comparison table the source table and repeat the above process.

How to Detect Duplicate Records in A Source Table

To use Compare-IT to detect duplicate records in a table:

1.  Activate Compare-IT by clicking on the Compare-IT button on the EMAGENIT toolbar.

2.  Be sure the table being used by Compare-IT meets the minimum guidelines specified in the Table Guidelines section.

3.  Determine the source table. The source table is the table that contains the possible duplicate records that you want to detect. Once the source table has been determined, click on a cell within its boundaries and then click the Set source table box. The headers of the table should automatically be selected at this point. This shows that Compare-IT locked onto the complete table. If the headers do not select completely or not at all, uncheck the Auto detect check box under the box you just clicked in, select the entire header row of the source table then click in the Set source table box again. This will manually set the source table.

4.  Click the Detect duplicate records... option button. Compare-IT in this mode will scan the designated fields looking for duplicate values. You can check the Flag data check box to flag the duplicate records or check the Enable output check box to output a table that is free of duplicates.

5.  Select the field to analyze in the 1st Source field drop down . You can also scan two other columns using the 2nd and 3rd source field drop downs.

Flagging Duplicate Records

6.  To flag the source records that are duplicates, check the Flag data check box. Duplicate records will be colored a light yellow. Note that this feature will operate in conjunction with the outputting feature discussed next. Uncheck the Enable output check box since you are not outputting at this point.

7.  Click the Compare button to highlight the duplicate records in the source table.

Creating A New Table

6.  Compare-IT has the ability to extract non-duplicate records from a source table and combine them into a new table. To accomplish this task, select an output cell to designate the new table starting position. Next click the Set output cell box to set the cell.

7.  Check the Enable output check box to create the new table.

8.  Click the Compare button to start building the table at the starting output cell. Note that all cell values in the output area will be overwritten without warning.

How To Match Records In Two Tables And Return Values

To use Compare-IT to match records in two tables and return values:

1.  Activate Compare-IT by clicking on the Compare-IT button on the EMAGENIT toolbar.

2.  Be sure the two tables being used by Compare-IT meet the minimum guidelines specified in the Table Guidelines section. If they exist in two separate workbooks, tile the workbooks in Excel for easy access. To tile a set of workbooks, consult Excel on-line help.

3.  Determine the source table. The source table is the one you want to compare against another table. Once the source table has been determined, click on a cell within its boundaries and then click the Set source table box. The headers of the table should automatically be selected at this point. This shows that Compare-IT locked onto the complete table. If the headers do not select completely or not at all, uncheck the Auto detect check box under the white box you just clicked in, select the entire header row of the source table then click in the Set source table box. This will manually set the source table.

4.  Determine the comparison table. The comparison table is the one you will be comparing against. Once the comparison table has been determined, repeat the procedure discussed in #3 to select it only click in the Set comparison table box.

5.  Click the Match…return to Comparison table option to read data from the source table to match and place it against the proper comparison record. Click the Match…return to Source table option to read data from the comparison table and place and match it against the proper source record.

6.  Select the source field in the Source field drop down that will be compared against a field in the comparison table. If you have repeating values in the 1st source field, select a secondary or even a third source field to compare by selecting their headers in the 2nd and 3rd source field drop downs.

7.  Select the comparison fields in the Comparison field drop downs below the Source drop downs. Be sure that if you are using two source fields you select two comparison fields.

Extracting And Pasting Field Values

8.  Compare-IT has the ability to extract field values from the source or comparison table and paste it in the proper record of the designated table (source or comparison). Which table is copied from and pasted to is determined in step #5 above. If returning data to the comparison table, designate the source field data to return by selecting its header in the Source extraction field drop down. If returning data to the source table, designate the comparison field data to return by selecting its header in the Comparison extraction field drop down.

9.  Select the table field header to start placing the extracted values in by clicking on the desired field header in either the comparison or source table (based on the choice made in #5). Next click the Set output cell box to physically set the cell. Note that it is best to place the new data after the last field of the table in a blank column. Just make sure the cell selected is in the same row as the table headers. Do not attempt to overwrite an existing field or insert a blank field in the table you are pasting to as this will cause the program to malfunction. If you want to overwrite a field with the values being pasted, consult the next section.

10.  To extract and paste the values, check the Enable output check box.

11.  Click the Compare button to extract the data and place it at the starting output cell.

*Note that all cell values in the output area will be overwritten without warning.

Inserting Values Into a Designated Field in a Table

8.  Compare-IT has the ability to extract field values from the source or comparison table and paste it in a record of an existing table field. Which table is copied from and pasted to is determined in step #5 above. If returning data to the comparison table, designate the source field data to return by selecting its header in the Source extraction field drop down. If returning data to the source table, designate the comparison field data to return by selecting its header in the Comparison extraction field drop down.

9.  If returning data to the comparison table, designate the comparison field to paste it in by selecting its header in the Comparison insertion field drop down. If returning data to the source table, designate the source field to paste it in by selecting its header in the Source insertion field drop down.

10.  To extract and paste the values, check the Enable output check box.

11.  To enable the insertion feature, check the Insert data check box.

12.  Click the Compare button to extract the data and insert it.

*Note that all cell values in the output area will be overwritten without warning and cannot be retrieved.

Comparing Values While Pasting

8.  Compare-IT has the ability to extract data from the source or comparison table, paste it in a record of an existing table field and also compare the pasted value against a designated field in the table returning a True or False value next to the pasted value. Which table is copied from and pasted to is determined in step #5 above. If returning data to the comparison table, designate the source field data to return by selecting its header in the Source extraction field drop down. If returning data to the source table, designate the comparison field data to return by selecting its header in the Comparison extraction field drop down.

9.  If returning data to the comparison table, designate the comparison field to compare the extracted value against by selecting its header in the Comparison extraction field drop down. If returning data to the source table, designate the source field to compare the extracted value against by selecting its header in the Source extraction field drop down.

10.  Select the table field header to start placing the extracted values at by clicking on the desired field header in either the comparison or source table (based on the choice made in #5). Next click the Set output cell box to set the cell. Note that it is best to place the new data after the last field of the table in a blank column. Just make sure the cell selected is in the same row as the table headers. Do not attempt to overwrite an existing field or insert a blank field in the table you are pasting to as this will cause the program to malfunction.

11.  To extract, compare and paste the values, check the Enable output check box.

12.  To enable the comparison feature, check the Compare check box then select the appropriate comparison criterion in the drop down.

13.  Click the Compare button to extract the data, paste it and compare it. Note that all cell values in the output area will be overwritten without warning.

How to Close Compare-IT

Compare-IT closes when Microsoft Excel closes and does not reopen with it. This is by design. To close Compare-IT so it is no longer present in Excel without closing Excel, just check the Quit check box at the bottom of its screen then click the Close button. This will remove Compare-IT from Excel.