Generic selectors
Exact matches only
Search in title
Search in content
Post Type Selectors
Search Products
Filter by Categories
B-BBEE
Blog
Company Secretarial
Featured
General Business
General Interest
Investing in Property
Marketing
Personal Growth
Personal Wealth
Tax
Trusts and Estate Planning
Uncategorized

For my sins, I’m the one who has to allocate debtor payments as we are sticklers for payment and it’s worth running this at partner level. But so often a debtor makes a lump payment against an account with lots of open items and without giving us a remittance advice. So which invoices did they pay?

I love a challenge and I tried all sorts of techniques to determine the combination of invoices that fits the total paid, but I fell flat.

Then I Googled “How can I find a subset of numbers that add up to a particular total” and Bingo!

This is what came up http://superuser.com/questions/204925/excel-find-a-subset-of-numbers-that-add-to-a-given-total 

Now, when you try it, it seems a bit complicated, but it is well worth the effort. The writer missed out just two little things that gave me trouble.

1) When entering the formula in the Objective (Target) cell, you type  (using his example) =SUM(A1:A100*B1:B100) then to get the curly brackets you must press <Ctrl><Shift><Enter>. You can’t type them in as he suggests. This sets up an Array formula, something that I still haven’t fully got my head around in Excel, but it amounts to a shorthand way of writing =SUM(A1*B1,A2*B2,A3*B3,………..A100*B100)

2) To add the constraints, you first click on Add, Then in the cell reference select the range that he indicates (or your equivalent), then from the <= dropdown select bin and click OK

Everything else is explained very well (the conditional formatting is a nice touch) and you can set up a master solver for use over and over again.

If you get stuck, give me a call on 011 656 2945 or drop me an email and I’ll send you the master that I set up for my use.

Should you wish to make an appointment, please feel free to visit derek’s diary  and book a time that suits you.

 

0 comments

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Looking for even more informative content? Check out the books I have written which have proved to be very popular.

Need help with your Trust or Business?

Contact us today or set up a free meeting with our CEO, Derek Springett. We have been offering expert advice and business services since 1971. You can also view see our full list of services.

Do you want to leave your cart?

Your cart is awaiting your next purchase, so please proceed to the Home page and continue shopping. If you are leaving your cart because of problems, why not give us a call on our 24 hr numbers 063 866 8928 or 011 805 0030 (subject to load shedding)? If all else fails, call Derek, our CEO on 082 552 9696. We’ll do what we can to help