Match cash entries to invoice entries by account

[TryBright]

Hi there.
I have a long list of invoices and cash payments which I need to find the offseting entries.
There are over 1,000 accounts and each account can have many entries, so over 20,000 lines of data, a long and laborious task which I have to do every week!
The lady before me did this manually, by printing off a rainforest of information and manually
matching them up, then going in to the system and alloating the entries against one another.
I have managed to get a download into excel, and have found vba which will match offsetting entries, but not by individual account and I cannot figure a way to do this. Can anybody help?
The format of the download is in columns, with the key data being account in column A, the debit in column K and the credits in column L
Account Account Name Date Type Reference CC M Debit Credit Net
004CON Mr Smith 15-Jan-14 PURC I302 26 17 50.82 0 50.82
004CON Mr Smith 16-Apr-14 PURC I468 26 4 232.32 0 232.32
004CON Mr Smith 19-May-14 CASH 1 0 0 50.82 -50.82
AMC002 MR CHARLES 14-Mar-14 PURC P964 98 9 2000 0 2000
AMC002 MR CHARLES 08-Apr-14 PURC P051 98 5 2550 0 2550
AMC002 MR CHARLES 08-Apr-14 PURC P052 98 5 6500 0 6500
AMC002 MR CHARLES 08-Apr-14 PURC P053 98 5 6500 0 6500
AMC002 MR CHARLES 29-Apr-14 PURC P120 98 3 2350 0 2350
AMC002 MR CHARLES 29-Apr-14 PURC P121 98 3 2500 0 2500
AMC002 MR CHARLES 29-Apr-14 PURC P122 98 3 5000 0 5000
AMC002 MR CHARLES 06-May-14 CHEQUE 98 0 0 2000 -2000
AMC002 MR CHARLES 06-May-14 C/Card 98 0 0 1550 -1550
AMC002 MR CHARLES 07-May-14 Deposit 98 0 0 1000 -1000

In the example above, for account 004CON the purchase for I302 and the cash for 50.82 offset.
For account AMC02, the purchase P964 and matches to the cheque for 2,000 and the purchase P051 matches the c/card payment of 1,550 and the deposit of 1,000.

Any help would be gratefully received.

Thanks

 

[shadow12345]

Hi and welcome to the board.

Couple of questions.... first, can you show us the code that you have that matches the offsetting entries for 1 account? ....

My first though, is split the accounts on to different tabs in the workbook... run the matching offset code you already have on every sheet, then recombine them together into one sheet. but if you let us see the code, we might be able to change it.

2nd question
Can you tell me the columns that each figure fits under? the copy and paste of that data doesn't sit correctly in my excel (there are tools avaialble on the board rules and information posts that show you how to upload a section of the spreadhsheet if thats easier)

*edit found the link for the spreadsheet to mrexcel add in http://cid-8cffdec0ce27e813.office.live.com/browse.aspx/MrExcel