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