I am sure I used a tool or formula a few years back for this.

I have a list of say 25 items. (column A)
In the next column (Column B) there is an amount against each item.
eg bread £5

I am looking for something that will give me the breakdown if I have a total
of say £195 as to how that total was achieved.(from what items)

On Feb 27, 7:46*am, "SS" <nonens...@blueyonder.co.uk> wrote:
> I have a list of say 25 items. (column A)
> In the next column (Column B) there is an amount against
> each item. eg bread 5
> I am looking for something that will give me the breakdown
> if I have a total of say 195 as to how that total was
> achieved.(from what items)

If you put the list of unique items (bread, milk, etc) into column C,
say starting in C2, and if the data is in A2:A26, put the following
into D2 and copy down as needed:

=SUMIF(A2:A26,C2,B2:B26)

"joeu2004" <(E-Mail Removed)> wrote in message
news:46de67fa-4104-4c77-9ca1-(E-Mail Removed)...
On Feb 27, 7:46 am, "SS" <nonens...@blueyonder.co.uk> wrote:
> I have a list of say 25 items. (column A)
> In the next column (Column B) there is an amount against
> each item. eg bread 5
> I am looking for something that will give me the breakdown
> if I have a total of say 195 as to how that total was
> achieved.(from what items)

If you put the list of unique items (bread, milk, etc) into column C,
say starting in C2, and if the data is in A2:A26, put the following
into D2 and copy down as needed:

=SUMIF(A2:A26,C2,B2:B26)

I am probably not explaining this properly although I will try the above and
see if it works.
It may well have been an `add in` type programme that did this for me in the
past.

In the previous one I put the total in and it showed me the values (£s) as
to how it arrived at that total.

eg I would type in say £101 and it would show the individual amounts that
would add to £101.

My apologies for being vague about this but it was a few years back I used
this.

On Feb 27, 6:32*pm, "SS" <nonens...@blueyonder.co.uk> wrote:
> "joeu2004" <joeu2...@hotmail.com> wrote in message
>
> news:46de67fa-4104-4c77-9ca1-(E-Mail Removed)...
> On Feb 27, 7:46 am, "SS" <nonens...@blueyonder.co.uk> wrote:
>
> > I have a list of say 25 items. (column A)
> > In the next column (Column B) there is an amount against
> > each item. eg bread 5
> > I am looking for something that will give me the breakdown
> > if I have a total of say 195 as to how that total was
> > achieved.(from what items)
>
> If you put the list of unique items (bread, milk, etc) into column C,
> say starting in C2, and if the data is in A2:A26, put the following
> into D2 and copy down as needed:
>
> =SUMIF(A2:A26,C2,B2:B26)
>
> I am probably not explaining this properly although I will try the above and
> see if it works.
> It may well have been an `add in` type programme that did this for me in the
> past.
>
> In the previous one I put the total in and it showed me the values ( s) as
> to how it arrived at that total.
>
> eg I would type in say 101 and it would show the individual amounts that
> would add to 101.
>
> My apologies for being vague about this but it was a few years back I used
> this.

Sounds like a school problem.... You'll have to do all
possibilities. Just a lot of loops. Most likely why you were asked.

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

On Feb 27, 3:32*pm, "SS" <nonens...@blueyonder.co.uk> wrote:
> In the previous one I put the total in and it showed me
> the values ( s) as to how it arrived at that total.

Sorry. I completely misunderstood your requirements.

Take a look at
http://www.sulprobil.com/html/accoun...e_problem.html .

Even though it is described as an Accounts Receivable problem, it
really is a solution to the general problem of determining which
subset of a list of numbers sum to a specified total.

Hope you can figure out how to use Bernd's implementation, really
attributed to Michael Schwimmer.

There is also a way to use Solver to generate a solution. But I
believe Schwimmer/Bernd's algorithm is faster and more reliable.

But be forewarned: there is no guarantee of a soluion with any
algorithm, and any algorithm might take an inordinate amount of time
to find a solution. In theory, an algorithm might need to consider
2^n combinations, where n is the number of individual values to be
summed -- 25, in your case.

"joeu2004" <(E-Mail Removed)> wrote in message
news:445e76dd-ff79-40e2-bf23-(E-Mail Removed)...
On Feb 27, 3:32 pm, "SS" <nonens...@blueyonder.co.uk> wrote:
> In the previous one I put the total in and it showed me
> the values ( s) as to how it arrived at that total.

Sorry. I completely misunderstood your requirements.

Take a look at
http://www.sulprobil.com/html/accoun...e_problem.html .

Even though it is described as an Accounts Receivable problem, it
really is a solution to the general problem of determining which
subset of a list of numbers sum to a specified total.

Hope you can figure out how to use Bernd's implementation, really
attributed to Michael Schwimmer.

There is also a way to use Solver to generate a solution. But I
believe Schwimmer/Bernd's algorithm is faster and more reliable.

But be forewarned: there is no guarantee of a soluion with any
algorithm, and any algorithm might take an inordinate amount of time
to find a solution. In theory, an algorithm might need to consider
2^n combinations, where n is the number of individual values to be
summed -- 25, in your case.


Thanks all thats exactly what I am looking for, that will do the job.
I am sure last time though it was part of the excel `add-in` package.
No matter though this will do as its a one off thing.

The last time I used similar it was indeed several invoices of differing
amounts and I had a payment but could not work it out.
It took just a few seconds for the programme (then) to do it.

Thanks again guys