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

  • Thread starter Thread starter EagleOne
  • Start date Start date
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
 
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
 
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!
 
[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.
 
Back
Top