Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
Find Combinations

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.

image

Posted: Thursday, November 12, 2009 10:11 PM by FredK
Filed under:

Comments

sam said:

Fred,

Did you know you can do this with the "Solver" Add-in that ships with Excel

Download this templates from Tushars Website

http://www.tushar-mehta.com/excel/software/download.shtml?../templates/match_values/template-set-match.zip

# November 14, 2009 8:31 AM

John K said:

Fred,

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

# November 14, 2009 11:06 AM

sandy.marshall@lyquidity.com said:

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.

# November 16, 2009 12:51 PM
Leave a Comment

(required) 

(required) 

(optional)

(required) 

  
Enter Code Here: Required

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Page view tracker