Combinations of Same Amount

[Syed Faizan Ali]

Dear All

I am looking for a formulae through which I can track combination of value from a given list of values.

For Eg:

List is 6,8,25,9,7,11,3,4,10,1,3,1,2

I want combination of values from the list given above that would equal 14.

Simple answer can be (8 & 6),(11 & 3),(10 & 4)


Please suggest some formulae?

 

[DonMinter]

How is the list stored in Excel? Is it a single text line that has to be parsed, or is it on a sheet that has the values in different cells; if so, are the cells via columns or rows?


The formula would depend on how the data is stored in Excel.


If you're just looking for suggestions, then iterate through the list.

Count the items to get "N" number of items.

For X = 1 to N-1,

For Y = X +1 to N,

Check if X + Y = (target value; 14 in your example)

Increment Y

Increment X


That's how I'd do the "pseudocode" before translating it to VBA.

 

[Luke M]

Sounds like a task for the Solver add-in. The trick will be figuring out how to setup the problem. Check out Chandoo's article here:

http://chandoo.org/wp/2011/05/11/using-solver-to-assign-item/