• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code for finding any combination of three numbers that add up to zero

G

gjl

New Member
I have an accounting problem. I need to clean up a list of about 17000 lines. Incoming and outgoing payments have to be matched, meaning the sum of a group of invoices is exactly zero. The system has matched about 200 of them and I found about 300 more where debit and credit are exactly the same. That leaves about 16.500 lines where the combination consists of three or more lines.


I am looking for a solution where I use VBA to identify any combination of three lines that add up to zero and mark those lines.

I found lots of references to this problem in general but they are very complex for me to understand and adapt accordingly. By limiting the number of lines to exactly three, I hope there is a simpler solution.
 
Hi Gert ,


I doubt that limiting the number of cells will make any difference to the algorithm , though it will certainly make a difference to the run time of the algorithm. In fact , any combination of 2 would have made it simple , but three or more makes it complex.


In general , most of the algorithms will use recursion to "simplify" the programming , though in the process , it can make the algorithm more difficult to follow.


Is this a one-time process or will you be doing this frequently ? This will decide whether the algorithm needs to be optimal or it can be sub-optimal.


Narayan
 
Also, is the number the only information that we can use, or is there also some text/description that could be used to help match things up?
 
The process is a one time process. I have to repeat it for several GL accounts. They told me around 10 in total. After that the new system takes over.

I also know for sure that not all entries will matches. Some are simply to recent.


The list I am working with is a system dump and contains all information that is available to me. There are columns for debit/ credit date and a few other fields which are mostly empty.

I can make some assumptions but haven't done that yet because of the added complexity

- a debit amount always has to be matched with two credit amounts.

- the date when the debit amount is posted to the ledger always precedes that of the credit amounts.


I did a quick calculation but 17000 lines yield over a trillion (10exp12) combinations, which is rather daunting.
 
Hi Gert ,


Can you post a sample workbook with about 300 rows of data , with only the relevant columns ? This will help to develop and test the algorithm.


What I can think of is to sort the data based on all debits first and then credits , and then as a second level the date. Thereafter , it may be possible to use a brute-force method of checking every combination within the date constraints ; it may not , after all , be a trillion combinations.


Narayan
 
You may also want to have a look at:

http://www.dailydoseofexcel.com/archives/2012/03/02/introducing-tm-match-target/

http://www.tushar-mehta.com/excel/templates/match_values/index.html
 
Sorry it took longer to upload the workbook, but here it is

https://dl.dropbox.com/u/83088130/Chandoo%20VBA%20accounting%20problem.xls


@Hui, I found those options when I googled, but I do not have enough VBA experience to adapt them to my specific situation. Thanks for the input.
 
Hi Gert-Jan


http://www.mrexcel.com/pc09.shtml has a Accounts Receivable tool from a Mr Excel Challenge - page down to IOANNIS's winning macro: section. I know it probably won't suit you right now as you need to know a bit about VBA to get it working for you. It also can be slow if the greater the numbers and larger the targets, but it does work. I think the Mr Excel blog has entries on people trying to get it going which might give you the guidence you need.


Cheers

John
 
Hi Gert ,


I have tried the following :


1. Adding two positive amounts and trying to match this total with one negative amount - No luck


2. Adding two negative amounts and trying to match this total with one positive amount - No luck


Are you sure that there are some matches in the data you have uploaded ?


Are you sure it is only a combination of 3 ? Can it be more than 3 ?


Narayan
 
Hi Narayan,


First of all thanks for your help. I really appreciate it.

I cannot be sure that there are any matches but from an accounting perspective it is highly unlikely that there are none. The only reason I can think of is that debit and credit are further apart in time and that the 300 lines sample was to small.


Of the 17000 lines in the workbook I was able to match about a 1000 on a 1:1 basis. I'll do a quick check to see how much time there is on average between two transactions and get back to you.


Once again thanks for your help.
 
Hi Narayan,


About five percent of the lines are matched by the system (I excluded them from the sample). They are always matched on the same date.

I manually matched amounts were debit = credit (1:1). Most of them match the same day. I have a few outliers which I think are accidental.

I did expect the sample I gave you to yield some results. It looks like I am stuck.

I've advised management to write off older years (seems reasonable since they match the same day).

We are going to focus now on SAP and try and solve the problem in the system.


Once again thanks for all your help. I really appreciate it.
 
You must log in or register to reply here.
Back
Top