+ Reply to Thread
Results 1 to 9 of 9

Using Excel to determine a combination of numbers that equal a set total

  1. #1
    Registered User
    Join Date
    03-04-2005
    Posts
    4

    Using Excel to determine a combination of numbers that equal a set total

    Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment. Not sure if this can be done but thought I would ask. I appreciate any help that can be provided.

    Thanks,
    DaveBab

  2. #2
    Forum Contributor
    Join Date
    03-12-2004
    Posts
    329

    Re: Using Excel to determine a combination of numbers that equal a set total

    Check this post, it came from a few years back but it's a similar problem to what you are having. However, I would think it probably does not help too much if you are dealing with account receivable since you are probably looking at hundreds and thousands of data. Good luck.

    http://www.excelforum.com/excel-work...for-a-sum.html

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,050

    Re: Using Excel to determine a combination of numbers that equal a set total

    See http://www.tushar-mehta.com/excel/te...ues/index.html
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    03-04-2005
    Posts
    4

    Re: Using Excel to determine a combination of numbers that equal a set total

    Thank you! This is a great feature. I typically have somewhat smaller data sets (~50), so it takes a bit to run, but can be very helpful. One question on the instructions. There is a formula B2:B100=0 in the example. Where would this be used? I couldn't find it in the solver spreadsheet example that was provided.

    Thanks,
    DaveBab
    Last edited by DaveBab; 01-30-2009 at 02:52 PM. Reason: Change of who I was responding to

  5. #5
    Registered User
    Join Date
    01-18-2013
    Location
    Christchurch New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Using Excel to determine a combination of numbers that equal a set total

    go to this site: http://www.handyexcelmacros.net
    Go to the Sum to target page
    Download the excel file.

  6. #6
    Registered User
    Join Date
    01-18-2013
    Location
    Christchurch New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Using Excel to determine a combination of numbers that equal a set total

    I wish to delete the above comments now because the website http://www.handyexcelmacros.net will be going down soon. Dredge.

  7. #7
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    2,117

    Re: Using Excel to determine a combination of numbers that equal a set total

    This seems to me to be the same as that placed in the post http://www.excelforum.com/search.php?searchid=4467096
    and for which I presented an Excel array formula for a sub-list of 9 elements

  8. #8
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    2,117

    Re: Using Excel to determine a combination of numbers that equal a set total

    Hi

    See the file Sample_6.xlsx

    Regards

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,912

    Re: Using Excel to determine a combination of numbers that equal a set total

    Jose, you are responding to a really old thread, I doubt any members are still following it
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think some-1 helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1