Link to home
Start Free TrialLog in
Avatar of wcody
wcodyFlag for United States of America

asked on

Search combination of numbers for a specific total

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?
NOV1300.xlsx
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of wcody

ASKER

All the numbers are listed in column b, and the out come I am looking for is in cell a
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.

Cheers
Rob H
ASKER CERTIFIED SOLUTION
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.