You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s author is Bill Seddon from Lyquidity Solutions, who informs us about a free tool for Excel users that allows you to find combinations of numbers that total to a selected value. For more information and a video, see http://www.lyquidity.com/findcombinations.
Imagine you are an auditor or cash accountant and need to reconcile cash balances to their invoices. Which combination of values makes up each cash value? Not surprisingly, the Find Combinations add-in has been popular with the audit firms.
Comments: (3) Collapse
Fred,
Did you know you can do this with the "Solver" Add-in that ships with Excel
Download this templates from Tushars Website
www.tushar-mehta.com/.../download.shtml
This nearly solves an annoying task I do. I am a Controller in a multinational company. We have many transactions (sales, credits, debits, etc.) with related parties but seldom send payments. Once in a while, I would like to reduce the number of open items by identifying positive and negative amounts that net to zero and clear those items.
Currently, I dump the open items into Excel, sort by value, and manually play with items until I get a combination that is nearly zero (it seems we never find a combination that is exactly zero).
Since my goal is to minimize the number of open items, not only do I need to net as close to zero as possible (a few dollars negative or positive can be simply written off), but I also try to include as many transactions as possible in the final answer. That is, netting two transactions for +5 and -5 is fine but netting four transactions for +1, +2, +2 and -5 is better.
It seems your Find Combinations would work if I put the goal as zero and there was a combination that equaled zero. Is there a way to set the goal to "as close to zero as possible."
John K
The utility will cycle through the number of combinations you specify so you can take the result that suits your needs (presumably the last result because it will include the largest number of combinations).
Is the issue that there are so many combinations it's not practical to skip over the early results? The current implementation cycles from two combinations to the number of value combinations you specify because it has to start somewhere.
However adding an option to cycle from the largest number of combinations you specify to the smallest would be straight forward and would present your preferred combination first.
Let me know if something like this would work.
Comments: (loading) Collapse