combination of sums equal to target value Solved

Question

Hello,
Using Microsoft Excel 2007 I want to do the following:
I have a database of values (numbers) and I have a target value. I want to find all the possible combinations of sums of the numbers (from the database) that equal the target value.

Is this possible?

Configuration: Windows 7 / Safari 535.11

eriiic · Accepted Answer

Hello,  Here is the latest version that deals with positive and negative numbers: https://mon-partage.fr/f/DVIe7uJu/  eric   You will never respond to an unsolicited message... Well, that's done.

eriiic · Answer

Hello,  Check if this program works for you: https://www.cjoint.com/?BEushEb54QO The explanations of the parameters are commented in the cells.  eric

eriiic · Answer

Hello,

Here are 5 columns.
Added:
- handling a negative target sum (without manual manipulation).
- a double click on a solution filters the relevant values.
- a double click on an active filter cancels it.

https://www.cjoint.com/?BEvjNdp8qA8

Normally debugged, but if you can test thoroughly and report any issues not too late...

eric

Adr1en · Answer

Hello,

I'm following up on this topic that interests me a lot,

I've taken your initial macro to try to modify it for my problem.
My issue: I have a list of numbers ranging from 1 to 33, and I want to find the best possible combinations to be either equal to 33 or as close to 33 as possible.
Knowing that each number must be used only once.

I hope I was clear in my explanation, and how can we modify it so that the numbers are no longer in euros, but integers?

Thank you in advance.

eriiic · Answer

Hello,  here it is: https://www.cjoint.com/c/CBmqwkGmHcg eric  -- You will never respond to an unsolicited DM... Well, that's done.

Roxane · Answer

Thank you very much!

Quick question: Would it be possible to allow the combination of several negative numbers and several positive numbers to reach a given result??

When combining a negative and a positive (e.g., to find -5, the system combines “-6” and “1”), it works.
But if we try several negatives with several positives (e.g., to find -17, the system would combine “-15”, “3”, “-7” and “2” =??), it doesn’t work.

HELP!!!

Roxane · Answer

Yes, but let's imagine we have a list of fairly high and precise numbers, with several digits after the decimal point; that would greatly limit the number of possible combinations... Is the idea still not feasible? 8-)

eriiic · Answer

Hello,  Yes, but let's imagine we have a list of fairly high and precise numbers, with several digits after the decimal, that would greatly limit the number of possible combinations.. No, it may decrease the number of solutions. But the number of combinations (they all need to be explored) is exponential. If you really have a lot of values, expect very long processing times, even extremely long.  In any case, it seemed like an interesting evolution to me, I tried... If you could give me feedback on how well it works. And out of curiosity, the number of values and the time taken. https://www.cjoint.com/c/CBom4p3UbC6  eric  -- You will never respond to an unsolicited message... Well, that's done.

Roxane · Answer

Thank you very much for your work!

Unfortunately, I can't open the link because it is blocked by the filter installed on the PC I'm working on. Is there a way to publish the Excel file in another way?

Thank you in advance.

Roxane

avilor · Answer

Thank you for the work Eric, your file helped me a lot. I did a test using a database consisting of 65 amounts, the target value contains 7 amounts, but after 3 consecutive tests of 3 minutes, no solution was found.
Why is there no solution, even though my database is small?

I would like an answer, thank you.

avilor · Answer

Hello Eric, regarding your question, yes, I followed the rules and I know the combination; it is just the addition of seven amounts, a very simple formula (a way to do a test).

eriiic · Answer

Good evening,

Actually, these are not crashes; it's more about regaining control of the macro during long calculations.
With 65 values, it can take a very long time, several hours... It has to explore all combinations, several millions.
If you set nbTermesmini=7, you exclude all combinations of sums from 2 to 6 terms, and you'll see that the solution is found quite quickly (in 30 seconds, without waiting for hours).

I changed the logic a bit and added a form that provides elements to see the progress of the macro. It also allows for pauses or stopping the analysis.
https://www.cjoint.com/c/DCrxbr4PYST

Eric

In addition to the thank you (yes, it’s done!!!), please remember to mark it as resolved when that's the case (at the top near your title).
Thank you.

PAPAWALKER · Answer

Good evening,
I am reading this post several months later. I retrieved the file and looked at the code (Well done ERIC, really)
I would like to know if it is possible to perform this analysis on a multi-column table?
Explanations (or at least an attempt :-) :
The range A4:A100 contains 96 values and a value in A2 equal to the sum of some of the values in column A
The range B4:B100 contains 96 values and a value in B2 equal to the sum of some of the values in column B
The range C4:C100 contains 96 values and a value in C2 equal to the sum of some of the values in column C

Can we search for the rows that can satisfy both the values of A2, B2, C2?

1°) The values are positive or negative integers
2°) The maximum number of rows to reach the sum is often <=3

If someone is reading this and knows the answer, thank you very much in advance

Best regards

Combination of sums equal to target value

13 answers

Gotek information

Texture issues in enshrouded

Self-invoking js function

My pc won’t charge anymore and runs super slowly

Download software against mcafee pop-ups

My computer stays connected for very little time

Unable to open xsl and xslx files

Archival hard drive no longer appears in file explorer

Samsung wallet

How to pair a receiver with an external speaker without fi