Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    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>
    Paul Coyle
    Approach love and cooking with reckless abandon

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    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...
    Paul Coyle
    Approach love and cooking with reckless abandon

  9. #9
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding Combinations (xp)

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

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    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 LastLast

Posting Permissions

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