Search combination of numbers for a specific total

Posted on 2011-05-13
Last Modified: 2012-05-11
Is there a way to enter a number in say cell a1 then have excel query through a set of several other numbers column b in this case to find the combination of the numbers in column b that totals out to the number I have entered into cell a1?
Question by:wcody
    LVL 31

    Expert Comment

    by:Rob Henson
    This is like the age old Hitch Hikers Guide to the Galaxy question. The answer is 42 but what is the question?

    Depending on the size of the data, there could potentially be so many different combinations that the question is nigh on impossible to answer.

    How many numbers to be selected? What if one of the numbers is equal to the answer? Will numbers be negative?

    Apologies if not helpful
    Rob H

    Author Comment

    All the numbers are listed in column b, and the out come I am looking for is in cell a
    LVL 31

    Expert Comment

    by:Rob Henson
    Apologies if you misunderstood my comment.

    I understood the question, the numbers are in column b and you need to know which add up to the value in a1.

    Your example shows over 500 numbers in column b, with positive and negative numbers.

    I assume you are wanting to find out which transactions make up the balance which you are entering in a1. You could start by shrinking the data by creating a pivot table based on id_num. This will enable you to strip out the id_num with zero balance.

    Rob H
    LVL 22

    Accepted Solution

    In this question:
    I wrote some code to do this. I changed it to apply for your situation (decimals and negative values). But there are just too many combination. When I try it for the first 20 values I get a result in about 15 seconds. But for the first 30 values I stopped the code after 15 minutes. So 600 values will take way to long. Maybe it is possible to optimize the code a bit, but hopefully it is possible to limit the number of options like robhenson suggests.
    You'll find my code in the attached file. Enter a number in Cell A1 and it will pop up with a msgbox with the possible combinations (only for values from the first 20 rows).

    LVL 19

    Expert Comment

    you could try to use the solver on this equation :

    say A2 contains the "starting row number"
    say A3 contains the "ending row number"
    say A4 contains the formula =SUM(INDIRECT("B" & A2 & ":B" & A3))

    you can then start from an educated guess and use the solver or a vb script to try out values for which the totals from B add up to the value you want.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now