Search combination of numbers for a specific total

Posted on 2011-05-13
Medium Priority
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 34

Expert Comment

by:Rob Henson
ID: 35754107
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

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

Expert Comment

by:Rob Henson
ID: 35754221
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

Nico Bontenbal earned 2000 total points
ID: 35754646
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

by:Arno Koster
ID: 35754717
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

864 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