Find Best combination of sum value or Nearest to sum value

[ExcelUser5005]

Hi,

I need an solution in VBA to find Best combination of sum value or Nearest to sum value from the list of data.

For ex:
in A column
A1 = 10
A2 = 20
A3 = 35
A4 = 50
A5 = 60

Given sum value
B1 = 100
B2 = 85

Expected output from macro
A1 = 10 B2
A2 = 20 B2
A3 = 35 B1
A4 = 50 B2
A5 = 60 B1

Total B1 is 95 which is near to 100. This is the best near combination of sum values
Total B2 is 80 which is near to 85. This is the best near combination of sum values

Kindly help

Thanks

 

[ExcelUser5005]

Can anyone help on this pls?

 

[ExcelUser5005]

Can anyone help on this?

 

[Eric W]

Hi,

This is actually quite a difficult problem. I've worked on several similar problems. See here for example:

minimum no. of cells in a row that add up exactly to a target number

Need to highlight the minimum no. of cells in the range starting from Column C till CX for each row, such that these add up to exactly the target number. Also would like to have the list of these numbers in descending order and the cell numbers of these numbers. In case same number is available...

www.mrexcel.com

The problem is that there's no standard algorithm for this. Sometimes for a given situation, there's a way to do it, but there's no general solution that works in a reasonable (linear, or even polynomial) time. The only way I know is to calculate all the possible combinations, and see which one works "best". For your simple example, it wouldn't be hard, but I suspect you have larger ambitions. It's possible that you could use the Solver. Next to the values in column A put another column that indicates which number goes with which sum, then have a formula showing how far off from the targets the sums are. Then minimize that value by varying the work column. But since this would not be a continuous function, the Solver might not actually check all the possibilities. I don't know enough about the internals of the Solver to say.

I suspect that one reason you haven't gotten any responses is that you're not just looking for a formula, or even a simple VBA macro, but a complicated algorithm. I also suspect there would be a lot of back and forth required to nail down the requirements, since I don't think that your example really encompasses your entire requirements. Most people here don't have that kind of time to devote to a single question.

I'm sorry to say I'm in that category too. I've tried my hand at several variations of this, and end up spending a lot of time on them, and usually do not end up with a satisfactory answer. I wish you well, and maybe someone else with try their hand at it. Good luck!

 

[ExcelUser5005]

Hi,

This is actually quite a difficult problem. I've worked on several similar problems. See here for example:

minimum no. of cells in a row that add up exactly to a target number

Need to highlight the minimum no. of cells in the range starting from Column C till CX for each row, such that these add up to exactly the target number. Also would like to have the list of these numbers in descending order and the cell numbers of these numbers. In case same number is available...

www.mrexcel.com

The problem is that there's no standard algorithm for this. Sometimes for a given situation, there's a way to do it, but there's no general solution that works in a reasonable (linear, or even polynomial) time. The only way I know is to calculate all the possible combinations, and see which one works "best". For your simple example, it wouldn't be hard, but I suspect you have larger ambitions. It's possible that you could use the Solver. Next to the values in column A put another column that indicates which number goes with which sum, then have a formula showing how far off from the targets the sums are. Then minimize that value by varying the work column. But since this would not be a continuous function, the Solver might not actually check all the possibilities. I don't know enough about the internals of the Solver to say.

I suspect that one reason you haven't gotten any responses is that you're not just looking for a formula, or even a simple VBA macro, but a complicated algorithm. I also suspect there would be a lot of back and forth required to nail down the requirements, since I don't think that your example really encompasses your entire requirements. Most people here don't have that kind of time to devote to a single question.

I'm sorry to say I'm in that category too. I've tried my hand at several variations of this, and end up spending a lot of time on them, and usually do not end up with a satisfactory answer. I wish you well, and maybe someone else with try their hand at it. Good luck!

Thanks for your reply