Find Combinations add-in
The add-in is developed using Visual Studio Tools for Office (VSTO) and Window Presentation Foundation (WPF). If you think an advanced custom add-in could make your Excel-based business process more effective, efficient or usable, contact us to find out how we can help ( This e-mail address is being protected from spambots. You need JavaScript enabled to view it ).
This high quality add-in is free
Not just 'free to download' but free to use: there will be no cost now or in the future.
We believe this add-in will be useful to accounting professional who use spreadsheets to reconcile balances. We provide it free so that when you begin looking for a spreadsheet management solution, we will not be strangers to you.
Introduction
Find Combinations is an Excel 2007 add-in which allows a user to find the combination of values in a specified cell range that total to a given value. The add-in is presented as custom task pane so it is always displayed while you need it and can be hidden at other times.
We're not pretending this functionality is unique because if you are an expert with Solver it can be rigged to provide the same result. However we do believe the way the options to select values, enter a total and watch the progress as alternative combinations are sought and presented makes it very easy for users who have to reconcile balances on a regular basis.
Video
Watch a short video to find out more about installing and using the add-in.
Signup
To watch the video or install the add-in you will be prompted to sign up or sign in if you already have an account on this site. Although the add-in has no monetary cost, we do ask you to provide professional contact details. You will need to enter details that can be validated to ensure you have access to the add-in and/or the video. If you provide incomplete details it is likely the web site will not enable your account for this feature
The add-in can be installed using the options on this page.
Using the add-in
The Find Combinations add-in is added to the Review tab. When you click on the Find Combinations button the add-in will present a custom task pane (see screenshot 2 below). The task pane allows you to:
-
Select a range of values
The selection identifies the cells values to be checked. The range must contain 2 or more cells to be valid and can include more than one area. Blank and other non-numeric cells will be ignored. But be careful, date are a number in Excel.
-
Enter a target value
Combinations of cell values will be computed and tested against this number for a match.
-
Maximum group size
This value allows you to control the maximum size of groups that will be tested. For example, although you are looking for combinations of numbers in a list of 100 values, you may know that the the number of values to be added together never exceeds, say, 5. You can then specify 5 as the maximum group size and then no groups of 6 or higher will be tested.
This is important, especially in large lists, because the number of potential combinations can be vast. There are 627 combinations of 2, 3, 4 and 5 numbers in a list 10. There are 2,369,885 combinations of the same groups in a list of 50 numbers and 7,937,339 combinations in a list of 100. On my 1.8Mz laptop working through these 7+ million numbers takes about 5 minutes.
-
Matches
The add-in will attempt to work through any problem given to it up to a limit 17,000,000,000 (17Bn) combinations though you might want to take an extended coffee break while this many are processed. This limit is reached using just the combinations of any six numbers from a list of 100 numbers of which there are over 1.2 billion.
As combinations are processed progress through the relevant combinations is indicated. You can halt the process at any time by pressing the Cancel button.
If a match is found, the process is interrupted and the matching combination of cells is highlighted (see screenshot 3). If the combination is the one you are looking for, press the cancel button. If it is not the one you require, press the next button.
