Trying every possible sum combination

**Caligula** · 03-23-2009, 12:01 PM

Hi all,

I have a project where I need to cycle through the number of rows to sum and which rows need summing.

For example, if column G has six values in it, I want it to first look for single row that equal zero and delete them. After that, I want it to look at any two rows that when added equal zero, e.g. G1+G(2->6) then G2+G(3->6). If that doesn't work, I want it to look at three rows, e.g. G1+G2+G(3->6), G1+G3+G(4->6), etc, etc.

I'm having a really hard time with the logic for this; everything I write duplicates the calc's or skips rows, etc. Does anyone have any code that already does something like this?

Thanks!
Chris

**shg** · 03-23-2009, 12:15 PM

How about posting a workbook with some examples?

**Caligula** · 03-23-2009, 12:22 PM

I've attached a sample of the data I'd be looking at. As you can see, there are 89 distinct values in column G, so there would be a lot of possible combinations. Hope this helps.

**shg** · 03-23-2009, 12:35 PM

So G2 contains 13,346.57, and you want to find one or more cells below that sum to -13,346.57, and zero all those out, then go down column G and repeat the process?

That's a non-trivial exercise. Tushar Mehta wrote some code that will find combinations that add to a total. You'd have to wrap that with some more code.

**Caligula** · 03-23-2009, 01:11 PM

That's exactly what I want to do. Do you know where I can find the code that he wrote ?

**shg** · 03-23-2009, 01:16 PM

Please Login or Register  to view this content.

**Caligula** · 03-23-2009, 01:48 PM

Thanks for the code, but either it's not working or I'm using it wrong. I copied it into a blank workbook and put the following numbers in h1:h8
10
15
-7
-3
6
98
53
231

I think threw this code into a commandbutton:

Please Login or Register  to view this content.

, but it didn't produce any results. I also tried even putting a -10 in there, to no avail. Thoughts?

**shg** · 03-23-2009, 02:16 PM

I've not studied the code, but at a glance I think you need to pass a non-zero total. For example, in your posted workbook,

Please Login or Register  to view this content.

Is it the case that you always want to find the sum of positive numbers to match a negative number, and vice versa (as opposed to numbers of mixed sign)?

If so, the numbers could be partitioned into two groups.