Matching up numbers

[Randall]

Is there a technique (in Excel or just general programming terms) for matching up numbers between one list and another?

When the same number appears in both lists it's easy. But what about sitautions like this: 2350 2000
350
400 150
650 900 I'd like to make the connections: 2350 = 2000+350 and 400+650 = 150+900. Pie in the sky?

I'm dealing with semi-messy real-world data, so I don't expect the lists to match perfectly. But it'd be nice if there was a way to do some of this programmatically.

As an alternative, if I total the numbers in each list and they aren't equal, is there a way to identify the numbers responsible for the mismatch?

Randall

[Wassercrats]

Would you always be adding two rows of the first column and comparing them with the corresponding two rows of the second, or do you want to start with one row and increase the number of rows each time there's no match? I don't know about Excel, but it's easy programmatically. Do you need it in a particular programming language?

[Randall]

Would you always be adding two rows of the first column and comparing them with the corresponding two rows of the second, or do you want to start with one row and increase the number of rows each time there's no match? I guess it would something like the second case.

In a nutshell, I'm comparing bank transactions (deposits in particular) with the client's own data. Sometimes there'll be one big deposit on the bank statement and two or three smaller transactions in the general ledger, other times it's the other way around. I may even be adding up several deposits from each list to get a match.

There is one helpful factor: all of them are linked to a date. Not always the same date, and sometimes they're scattered over a couple of days. But it does give me a way to narrow the field.

Randall

[Wassercrats]

I was afraid of that. It would be limited in the same ways a diff is limited since there may be a mismatch. If you have:

05 10
10 05
05 10

Would it be the first two rows that match with the third being the mismatch, or would the first be the mismatch and the second two rows match? Only an approximation is possible.

[ryount]

Randall, sounds like we deal with similiar issues, never have found a solution. It's prevents us from doing some bank recs in our g/l system. Typically for us we will have multiple deposits for the same day. It gets really screwy when a portion of a day ends up in transit to the next month.

[Wassercrats]

This would be even more complex than the various diff algorithms because you'd have to consider the sums of consecutive items from both sides, and the user would have to input the number of elapsed days that would indicate a no-match. The most complex part would be the algorithm, but that probably exists. The most important part to make it at least a little functional would be unique to this type problem and not relevant to a diff. The fancy algorithm would just make it much more accurate, but still a little buggy beyond repair. It would help though. There should be software for this. :dunno:

[Randall]

Randall, sounds like we deal with similiar issues, never have found a solution. It's prevents us from doing some bank recs in our g/l system. Typically for us we will have multiple deposits for the same day. It gets really screwy when a portion of a day ends up in transit to the next month. You don't even want to know just how screwy this client can be. Even if I can't get Excel to do the hard stuff for me, the program I've been writing is threatening to eliminate a good 6-7 days I spend doing their bank recs. With the G/L data from their system and the bank transactions from the bank's web site, I may never have to look at a stack of fanfold ledger paper ever again. :winky: There should be software for this. Better still, the client should have a decent accounting system. It's a real pain in everyone's butt -- me, my boss, and the client who has to spend hours running off the hardcopy. Now he can just print it to a file and email it.

It's taken 20 years, but we'll get these people into the 21st century one way or another.

Randall

[Andilinks]

There is software for this.

http://bankreconciliation.com/

[Randall]

BankReconciliation.com imports your Bank and General Ledger data and matches them at approximately 4,000 records per minute.

Then, you can identify, track and resolve exceptions. I'm downloading the trial version even as I type this.

Can't hurt to try it, and $500 for the desktop version would be a more than reasonable price if it can do the job better and/or more easily than my Excel-and-duct-tape solution. (Not than it makes me any less proud of my work.)

Looks like the trial is good for 15 uses, as opposed to 15 days, which should give me plenty of time to stress-test it on actual data.

Heck, I haven't even spent 15 days on my "super macro" yet.

Thanks for the tip. :yeah:

Randall