Macro to input value and find all solutions of addition to make that value.

[Winston0301]

Hi All,

I was wondering if there was a way I could input a number, select a data array and then get a macro to tell me every possible solution of adding those numbers in the selection together to make the input value.

So if I had a selection

1, 2, 3, 5

And I input 5 I would to want the macro to return 2 + 3 on one line then 5 on another line and then end the macro.

Hopefully this makes sense! Thanks in advance.

 

[Eric W]

This is a variant of the Subset Sum problem, which is known to be NP-Complete. (That's computer science talk for "it's really hard, and no one knows a really good way of finding the answer.")

But there are ways to solve it, usually involving trial and error. These usually work fine if the data array isn't too big. How big is your data set going to be? How do you want to enter the data set? In a group of cells on a spreadsheet, as an array in the VBA code, entered in an inputbox?

 

[Winston0301]

Hi Eric,

Thanks for your response. The data array will normally be about 200 rows long. Is it possible to select the array on entering the macro?

Id like an input box for the number I need and I want it to return all possible solutions if possible.

Thanks again.

 

[Gary's Student]

I asked a similar question back in 2005 and got a great response from John Coleman:

https://groups.google.com/forum/?hl=en#!topic/microsoft.public.excel.programming/4C_yJDWDvlk

Once the table of combinations is generated, the target value is subtracted from the some of each row and the result is then filtered to display the zeros.

 

[Eric W]

Very nice reference, Gary's Student. Better than the code I was thinking about.

Winston, you can take that code, and you'd need to add a check to see if a given subset equals your target number. However, at 200 rows, I think the macro would take a very long time to run. You'll have to add some efficiency improvements, such as: is the sum of elements 3 and 7 greater than my target? If so, I don't need to check any subsets with those 2 elements, no matter what size. That kind of macro improvement is a bit beyond what I can do through this forum.

Good luck!

 

[Winston0301]

Hi both,

Thanks for your responses, that code looks similar to what I need in listing all the items.

Im not really in tune with VBA, in saying that I'm really a newbie, I can do basic stuff with it such as using file dialogs, ifs and error trapping but beyond that I'm not really any good haha.

Do either of you know how to write the code where it looks for every solution to a subtotal?

Thanks again a in for your responses.