Home >Excel > Software > Match Target > Help
Google
Web This Site

TM Match Target

Purpose of the add-in

Installing the TM Match Target add-in

Using TM Match Radar

Status updates during analysis

The result

Registering TM Match Target

Things to consider

 

 

Purpose of the add-in

This add-in analyzes a list of numbers and finds combinations that sum to a given total.  This has applications in a range of disciplines including processing receipts, reconciling payments such as health care insurance reimbursements or payments by a customer for many outstanding invoices, operations management and operations research, and supply change management.

 

 

Installing the TM Match Target add-in

Note that the self-installer version also installs an uninstall capability.  To uninstall select Windows Taskbar | Start | Control Panel Add or Remove Programs... | scroll down to the TM Match Target entry.

If you chose to download the zip version, unzip the add-in file to a directory of your choice.

For installation instructions see common installation instructions.  In Excel 2003 load the TM Match Target add-in.  In Excel 2007 or later, load the TM Match Target (Ribbon UI) add-in.

 

 

Using TM Match Target

In Excel 2007 or later, select TM tab | Utilities group | Match Target button.


Figure 1

In Excel 2003 or earlier, select TM | Match Target Start... 


Figure 2

This will bring up the TM Match Target dialog box (Figure 3). 


Figure 3

Number of desired solutions: Specify the desired number of matches (combinations).  Use zero (0) to see all matches.  Enter a number or refer to a single cell containing a number.

 

Target value: This is the total that each combination sums to.  Enter a number or refer to a single cell containing a number.

 

Values to evaluate: This is a single range in a single column containing only numbers.  The add-in tests combinations of these numbers to see which add up to the target total.  In the example in Figure 3, this would be C5:C704.

The organization of the data can improve the search performance.

  1. If there are negative numbers in the list of numbers to search, they should appear first.
  2. For positive numbers, arrange them in descending order, i.e., the larger numbers should be first.

 

Start cell for results: This is the 1st cell used for the results.  The final range required to display the results can be fairly large depending on the number of combinations and the required options.

 

Show indices: With this option checked, for each combination that matches the target value, the add-in will list the indices of the matching numbers.

 

Show values: With this option checked, for each combination that matches the target value, the add-in will list the matching numbers.

 

Show IDs: With this option checked, for each combination that matches the target value, the add-in will list the IDs of the matching numbers.  This, of course, requires that each entry in the list of numbers to match has an unique ID associated with it.

 

IDs Range: If the numbers available for matching have unique IDs, provide that range in this field.  This would be B5:B704 in Figure 3.

 

 

Status updates during analysis

The add-in displays a dialog box showing the number of matches found.  Use the Cancel button to stop further processing and see the matches found so far (Figure 4).


Figure 4

If the number of matches exceeds the number of rows available to show the result, the add-in will include an alert (Figure 5).


Figure 5

If the number of matches exceeds the number of rows available to show them, the add-in will offer a choice of listing however many will fit or to skip showing any of the matches as in Figure 6.


Figure 6

 

 

The Result

Depending on the options chosen (Show indices, Show values, and/or Show IDs), the add-in will create a result similar to that in Figure 7.  The columns labeled Target and Time are shown irrespective of the selected options.  The columns labeled Index {N} are shown when one selects Show indices.  Similarly, Value {N} and ID {N} are shown when one selects Show values and Show IDs respectively.

The most useful is the list of indices since from that one can derive the other results as well as carry out other analysis if required.


Figure 7

 

 

Registering TM Match Target

The add-in comes with a trial period.  To continue using the add-in after this trial period, please register it from the add-in's web page.  Once you get the registration key, use the Register... button add the key to the add-in as in Figure 8 and Figure 9.


Figure 8


Figure 9

 

 

Things to consider

  1. Finding combinations of numbers that total to a given value is a type of problem that requires a search of every possible combination to decide which combinations match the desired target.  The number of combinations to search increases as a power function.  If there are N numbers to search, the number of combinations to search is 2 to the power of N, i.e., 2N.  This becomes a very large number very fast and require a long time to enumerate all the combinations.
  2. There are certain optimization techniques that one can use to speed up the search.  Some of these are already included in the code.
  3. In addition, the organization of the data can improve the search performance.
    1. If there are negative numbers in the list of numbers to search, they should appear first.
    2. For positive numbers, arrange them in descending order, i.e., the larger numbers should be first.
  4. The code will list all the combinations it finds in a table with each combination occupying one row.  So, if the result contains more combinations than can fit in the worksheet, the code will only show those that fit.
  5. If the final result includes a large number of combinations, and particularly if the combinations contain many numbers, there is a possibility that Excel will run out of memory .  In this case, if you are using Excel 2010, consider using the 64-bit version of the program.  It will enable Excel to handle larger result sets.