Problem: Finiding which numbers from a list add to the total

**hriggs** · 03-17-2009, 06:59 AM

Hi,

I've got a list of numbers in the attached sheet, and am having a small issue that I was hoping you would be able to help with.

The list of numbers in column A represent the value of investment holdings.
Out of the list of numbers in column A, a number 'x' of these may sum to the figure in B1.
Unfortunately the figure in B1 may be made up of partial amounts of these figure, in which case solving this will be impossible
If this is not the case, and the figure in B1 is made up of the whole figures in column A, is there a way to find out which of these adds to this total?
Is there a function within excel, or a piece of vb code for this?

Thanks very much in advance

hriggsproblem.xls

**dominicb** · 03-17-2009, 07:14 AM

Good morning hriggs

Originally Posted by hriggs

the figure in B1 is made up of the whole figures in column A, is there a way to find out which of these adds to this total?
Is there a function within excel, or a piece of vb code for this?

OK, it's a case of good news and bad news I'm afraid.

The good news is yes it can be done.
The bad news is it's not going to work on a column with nearly 200 entries

.

There is a techinique detailed here that uses recursion to determine which numbers from a sequence add up to a prescribed number. Twnety items on the list seems about the limit after that, you're looking at an absolute age to calculate. Just put into perspective, if your list had 30 items, then that's a possible 1,073,741,824 combinations.

HTH

DominicB

**AlfD** · 03-17-2009, 08:58 AM

Hi!
I remember from a few years ago a competition which ?Bill Jelley's site ran each year and where, one year, the challenge was to do pretty much what you are seeking to do.
This url takes you (well, it takes me...) to the page with the winning solution. OK: the challenge was to find which of 54 items added up to the right "answer". I also remember running the code and being duly impressed.

http://www.mrexcel.com/pc09.shtml

Alf

**AlfD** · 03-17-2009, 09:41 AM

Hi again!

... and Ken Wright - July 2005 - provided a different solution on this very forum.

Alf

**hriggs** · 03-20-2009, 10:15 AM

Thanks for the replies all.

I've tried to get some of the codes to do this but it's going to take forever with all the entries I have, sometimes it's thousands!

I'll have to do it manually I guess, oh well :S