Anyone know of program or Excel VBA to examine set of numbers for Sum match?

E

EagleOne

2003 & 2007

The list of numbers below is just for an example.

I am attempting to find a set of numbers that add up to a given amount i.e.; In this case, I know
the answer because I created the amount.

I realize that this could be a real processor hog- but is it reasonably possible in the PC world?

2,767.16
62.75
308.00
60.66
1,745.70
791.20
(2,767.16)
(180.00)
(90.00)
(180.00)
(104.86)
(104.86)
(65.38)
(43.65)
(112.09)
(13.48)
(200.01)
(19.91)
(12.68)
(6.12)
(99.52)
(23.40)
(1.24)
(32.12)
(12.26)
(4.35)
(0.12)
(0.06)
(0.06)
(1,745.70)
(791.20)
(44.16)
(730.29)

Total (1,649.21)

What combination of the above will add up to i.e.: 1,539.45?

Answer:
-200.01
-.0.12
-6.12
1745.20

Any help or ideas is very helpful.

TIA EagleOne
 
M

Moe Green

Couple of approaches here...
http://www.tushar-mehta.com/excel/templates/match_values/index.html#Solver_Template
Moe



<[email protected]>
wrote in message
2003 & 2007
The list of numbers below is just for an example.
I am attempting to find a set of numbers that add up to a given amount i.e.; In this case, I know
the answer because I created the amount.
I realize that this could be a real processor hog- but is it reasonably possible in the PC world?

2,767.16
........
(730.29)

Total (1,649.21)
What combination of the above will add up to i.e.: 1,539.45?
Answer:
-200.01
-.0.12
-6.12
1745.20
Any help or ideas is very helpful.
TIA EagleOne
 
C

catpol

Joined
Dec 30, 2012
Messages
6
Reaction score
0
The number of possible combinations that we have to examine for a target sum depends on the number of transactions in your list.
It's quite large. The number of combinations is 2^(count of elements in your list).

There is a shareware add-in on ReconciliationWizard page.

Using this add-in I found that there are more that 1000 possible combinations of numbers from the in of TIA EagleOne's post that add up to 1,539.45... and then I got tired of clicking 'Next page'.

The largest subsets adding up to the target sum of 1,539.45 contain 26 amounts from the list and there is 4 of them. Then there are 5 containing 25 amounts ... and so on...

cheers!
 
A

AlBear

Joined
Dec 30, 2012
Messages
2
Reaction score
0
[email protected] said:
2003 & 2007

The list of numbers below is just for an example.

I am attempting to find a set of numbers that add up to a given amount i.e.; In this case, I know
the answer because I created the amount.

I realize that this could be a real processor hog- but is it reasonably possible in the PC world?

2,767.16
62.75
308.00
60.66
1,745.70
791.20
(2,767.16)
(180.00)
(90.00)
(180.00)
(104.86)
(104.86)
(65.38)
(43.65)
(112.09)
(13.48)
(200.01)
(19.91)
(12.68)
(6.12)
(99.52)
(23.40)
(1.24)
(32.12)
(12.26)
(4.35)
(0.12)
(0.06)
(0.06)
(1,745.70)
(791.20)
(44.16)
(730.29)

Total (1,649.21)

What combination of the above will add up to i.e.: 1,539.45?

Answer:
-200.01
-.0.12
-6.12
1745.20

Any help or ideas is very helpful.

TIA EagleOne
Click to expand...

If you have 30 numbers and you need to test all combination to find the one that adds up to a certain value that is a typical matematical problem that has been around for ages. There are Millions of combinations to test and of course a computer can do it but it takes a very long time.
You can use Solver in Excel but it will give you only one solution at a time and it is not practical.
You have to realize that a VBA Macro as some Excel add in offer would be extremely slow. You need computational power and more, digital manipulation is the technique used for this problem in supercomputers. Moreover: some thesis have been written on the problem but without finding the "magic alghorithm"
You could try SumMatch from SumMatch.com that has a trial version so you can see if it suits your needs.
 
Top