1. ## Finding Combinations (xp)

I have a column of amounts and want to list all the possible combinations from these amounts. So for ten values I want to list all the possible selections of 1,2, 3, etc. items from this list. Any ideas how to achieve this?
My intention is to find the possible combinations that would total to a specific amount. For example, from values of 1500,2002,1000,1100,500, etc., I want to find the possible combinations that would total, say, 4000?? Andy.

2. ## Re: Finding Combinations (xp)

You will be luck to get an answer on this query...
I have not come across any solutions based macro to do this type of task on the net. However...its hard to really know whats out there!
Also, I have never delved very deep into the analysis tools, but I doubt there will be something there too!
It will need to be customized! And that will be quite a task too with all the possible iterations!
Hope something pops up!

3. ## Re: Finding Combinations (xp)

I don't know of anything offhand (I'm sure it's "doable" though) but you might be getting into lots & lots of calculations, depending on how many items you're considering. For 10 amounts, there are 2

4. ## Re: Finding Combinations (xp)

If your numbers are in column A, starting in A1, and the sum you want to find is in B1, then the first VBA routine will find the combinations of two that add up to the number in B1, and the second routine will find combinations of three:

<pre>Option Explicit

Public Sub FindCombos2()
Dim I As Long, J As Long, lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row - 1
For I = 0 To lLastRow - 1
For J = I + 1 To lLastRow
If Range("A1").Offset(I, 0).Value + Range("A1").Offset(J, 0).Value = Range("B1").Value Then
MsgBox Range("A1").Offset(I, 0).Value & " + " & Range("A1").Offset(J, 0).Value _
& " = " & Range("B1").Value
End If
Next J
Next I
End Sub

Public Sub FindCombos3()
Dim I As Long, J As Long, K As Long, lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row - 1
For I = 0 To lLastRow - 1
For J = I + 1 To lLastRow
For K = J + 1 To lLastRow
If Range("A1").Offset(I, 0).Value + Range("A1").Offset(J, 0).Value _
+ Range("A1").Offset(K, 0).Value = Range("B1").Value Then
MsgBox Range("A1").Offset(I, 0).Value & " + " & Range("A1").Offset(J, 0).Value _
& " + " & Range("A1").Offset(K, 0).Value & " = " & Range("B1").Value
End If
Next K
Next J
Next I
End Sub
</pre>

5. ## Re: Finding Combinations (xp)

I was trying to nut out something similar last night Legare. Unfortunately my brain was bringing up BSODs due to the late hour. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I'm sure I've done this kind of thing before in an array, but using a recursive method - similar to building up Pascal's Triangle. I'll try to scratch the head at < the witching hour.

Alan

6. ## Re: Finding Combinations (xp)

Andy, several years ago I cam across an add-in that did just that. I used it to help people reconcile accounts, take a list of amounts and see if any combinations added up to the target value.
The anme in my Add-ins lists is Solver.xla, so if you cannot find this with a google search come back to me and I will send this to you at your private email address, as it is over the limit to attach here. HTH <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

7. ## Re: Finding Combinations (xp)

Solver.xla is a standard add-in that comes with Excel. It can be activated through Tools | Add-Ins...

If I remember correctly, you can decide whether or not to install it with Excel, so if it is missing from the list, you may have to run the Office installation and add this component.

8. ## Re: Finding Combinations (xp)

Thought I had something. Just looking at a file called FindUtility.xls which may not be exactly what you want but then again...

9. ## Re: Finding Combinations (xp)

Hans, can you tell me how to use the solver to accomplish a task such as this - - finding combinations out of a list that add up to a particular value?

10. ## Re: Finding Combinations (xp)

I just reacted to <!profile=pccoyle>pccoyle<!/profile>'s reply. I don't know if Solver can be used to solve the original problem.

If you post a concrete example, we might be able to come up with something.

11. ## Re: Finding Combinations (xp)

Personally, I would think Legare's macro approach would be the way to go.

Steve

12. ## Re: Finding Combinations (xp)

I had to do something similar to determine which possible combinations of elements could create a portfolio with a maximum and minimum range. I have attached a spreadsheet with some dummy information and some code.

The sheet assumes that the possible elements to be selected are listed by a code (any single-character typographic element - upper / lowercase letters, numbers, or punctuation - all together, you have about 60 possibilities), in row 6 extending to the right of C6. If there is a longer name, that is entered directly below the code in row 7, while the value of the element is in row 8. The target range is set in cells C2:C3 - if you are seeking a single value, enter it in both cells. You can limit the number of elements to be included in C4 - if this is zero the default condition is that the results can have as many elements as are present in the list of entires.

On clicking the "create" button the code iterates through all possible combinations of the entries, and writes the code letter and the corresponding value in column E and F, respectively, starting at row 22. If you want to replace those code with the names of the elements taken from row 7 highlight the desired codes and click the "Replace" button - "ABC" will be replaced by "Alice, Bob, and Charlie" in this example. It also reports how long the operation took - this is ahold-over from when I was comparing different approaches, but there is no harm in leaving it in.....

I have one question for the experts here. I tried setting the data types for the size() array and the MaxSize and MinSize variable to Currency, instead of Double - I was concerned that floating point representations might not be exactly equal, and a valid match would be discarded. When I do this, however, the WorksheetFunction.SumProduct(P(), Size()) operation always returns "0." Is there a problem with using the Currency data type in a worksheetfunction? I (thought that I) tried every variation of setting the different arrays and variables as Currency in case it was a data-type conflict and so on, but it always returned a zero - I settled for code that works...

Possible refinements:
<UL><LI> include an explicit 'epsilon' value to report a match

13. ## Re: Finding Combinations (xp)

Hi Dean

Without looking at your code in detail, I'd say that your general concern is correct:<hr>I was concerned that floating point representations might not be exactly equal<hr>
Floating point numbers should never be compared on an equality basis, because it's impossible the determine exactly how they will be stored down to the nth decimal place. Always better to use (Not) < or > comparisons.

But it's also inappropriate generally to use floating points where integers are the only thing that make sense, such as array indexes. In your code, you could/ should specify MaxSize etc. as Long, rather than Double. I'd imagine that Excel did the appropriate conversions anyway in this case; but using floats in place of ints generally can generate "unexpected" results.

Alan

14. ## Re: Finding Combinations (xp)

I will speculate that the currency type does not work with "excel" well since it is a "scaled integer"

It is actually stored as an integer that is 10000x too large. VB manipulates it to "move the decimal" over 4 places to make a number with 4 decimal points.

With the worksheetfunctions, you are using the excel functions, not VB functions, so it is not surprising that it could be confused.

Steve

15. ## Re: Finding Combinations (xp)

<hr>But it's also inappropriate generally to use floating points where integers are the only thing that make sense, such as array indexes. In your code, you could/ should specify MaxSize etc. as Long...<hr>

All the indices were dimmed as either integers or longs, as appropriate. MaxSize (and MinSize) are doubles becasue they represent the largest and smallest values that will be considered an acceptable result - unless the problem domain is restricted to integers, they have to be some sort of floating point number. I agree that you might run into problems trying to test for equality in floating point representations - but that is inherent in any soution to this problem.

I am not sure what you mean by using comparisions, instead of testing for equality - if the issue is that you are testing a result to see that it is equal to 15.3, and the representation of the result is stored as 15.300000000001, testing for an inequality will not help. The only way to deal with this is to test:
<pre>if abs(test - target) <= delta then
test passed
else
test failed
end if
</pre>

where "delta" is some suitable small number, larger than the representation errors on the platform you are using.

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•