Add-ins: Excel
PowerPoint

Installing the TM Match Target add-in

Status updates during analysis

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.

Note that the self-installer version also installs an *uninstall*
capability. To uninstall select scroll down to the 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 add-in. In Excel 2007 or later, load the add-in.

In Excel 2007 or later, select

tab group button.

Figure 1

In Excel 2003 or earlier, select

Figure 2

This will bring up the **TM ** 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.**

**If there are negative numbers in the list of numbers to search, they should appear first.****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.

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

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

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

- 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., 2
^{N}. This becomes a very large number very fast and require a long time to enumerate all the combinations. - There are certain optimization techniques that one can use to speed up the search. Some of these are already included in the code.
**In addition, the organization of the data can improve the search performance.****If there are negative numbers in the list of numbers to search, they should appear first.****For positive numbers, arrange them in descending order, i.e., the larger numbers should be first.**

- 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.
- 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.