Match cash entries to invoice entries by account

TryBright

New Member
Joined
May 23, 2014
Messages
1
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

Well-known Member
Joined
May 10, 2004
Messages
1,238
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
 
Last edited:

Forum statistics

Threads
1,082,145
Messages
5,363,384
Members
400,734
Latest member
sanpr

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top