Need way to see which combinations of which fields equal given number

C

Cordes

I am in need of a way to have Excel examine all the records in a column and show me all the combinations of any count of those records that will equalor sum up to a particular value. (Summing different combinations of records manually in Excel is proving to be far too time consuming.

Can this be done? Does anybody have any suggestions? Thanks in advance.
 
F

Frank Kabel

How many values do you have in this range. If you have
more than (lets say 100) not possible IMHO (at least not
within a reasonable time)
-----Original Message-----
I am in need of a way to have Excel examine all the
Click to expand...
records in a column and show me all the combinations of
any count of those records that will equalor sum up to a
particular value. (Summing different combinations of
records manually in Excel is proving to be far too time
consuming.)
 
J

Jack Sons

Frank,

If there were say no more than 25 entries, wehat would the procedure be?So
in A1 up to A25 positive (integer?) numbers and a "target" number in say
A30, what procedure would place in Col.B x-es or check marks in the rows of
those A-numbers of which the sum equals A30, and also in col. C for a
different conbination and so on until all possible combinations are found.
Does the problem become very complicated if the A-numbers are not unique?
Same for negative an zero allowed? Probably non-integer makes little
difference?

BTW, are you ever looking not on your PC screen? My compliments for the way
you help anybody.

Jack Sons
The Netherlands
 
F

Frank Kabel

Hi Jack
first the problem is where to store the results.
There're
2^25-1 = 33,554,431 different combinations (if you use a brute force
algorithmn)

if you could check 100 combinations per second this would require
approx. 4 days if you want all possible combinations which add up to
your sum

A brute force algorithmn would simply loop trough the array, adding up
one combination an check if this is equal to the desired sum. If yes,
store this combination somethere. This won't be affected by the type of
numbers
 
J

Jack Sons

Thanks Frank.

Jack.
Frank Kabel said:
Hi Jack
first the problem is where to store the results.
There're
2^25-1 = 33,554,431 different combinations (if you use a brute force
algorithmn)

if you could check 100 combinations per second this would require
approx. 4 days if you want all possible combinations which add up to
your sum

A brute force algorithmn would simply loop trough the array, adding up
one combination an check if this is equal to the desired sum. If yes,
store this combination somethere. This won't be affected by the type of
numbers
Click to expand...
 
Top