wcody
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
NOV1300.xlsx
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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