Check the bank ledger and bank statement.
Hello everyone, I'm making tea with cold water, and I saw a help-seeking post on a forum in the past two days.,[Help] Please help see how to modify this paragraph**How to modify the question of running water matching。
*Don't look at it, let's look at his**, it's like this:
His request is (sic):
I haven't been to the forum for a long time, I recently encountered the problem of checking the details of the bank statement and Kingdee export, I want to ask you how to deal with it, there is a paragraph above**, but this paragraph** can only check whether the credit data on both sides of the 1 credit data is the same, if there is, it is clear that the same data is the previous date, but the effect I want is the debit on the left, the credit data date, and the debit, credit, and date on the right to check the duplicate value, that is, the date amount is checked together, when the date When the amount is consistent, it is considered a duplicate, and the corresponding data on both sides is cleared, and the amount is matched, I hope that it will not be matched because of the thousandth sign and no thousandth sign, thank you very much!His request is to clear the data of the same date and amount, leaving the account unpaid. But I don't think that's a good thing to do.
I think it's better for us to mark the records that match the checks, and after the checks are completed, the data that is not marked is not reached. Also, checking according to the same date is not too strict, and it doesn't feel like there's much need.
Still, I wrote a ** to clear the same record as he asked:
sub check() dim ws as worksheet dim arr() dim lastrow as long dim lastcol as long set ws = sheets("sheet1") with ws lastrow = .usedrange.rows.count lastcol = .usedrange.columns.count arr = .range("a4:m" & lastrow).value end with for i = 1 to ubound(arr) if arr(i, 3) In addition, I redesigned the form according to my own ideas, using the way of marking the same amount, regardless of the date, and made a simple bank reconciliation details record check check gadget, to share with you:
The basic idea and implementation process.
1. Redesign the header and standardize the fields: corporate debit, corporate credit, leftcheck, bank debit, bank credit, rightcheck, which are key fields and cannot be modified.
On the left is the Corporate Bank Journal:
On the right is the bank statement:
2. Before we check it, we must clear the existing check mark. Let's click "check" and run the reconciliation process. After checking, click "Not Reached" to copy the unmarked data to the "Unreached" table for the preparation of the bank balance reconciliation statement. After we automatically check, we can also manually check, manually mark one-to-many, or even many-to-many records (fill in the content in leftcheck, rightcheck), and then extract the outstanding accounts.
3. Check the check process:
1) Load the detailed data into the array arr. The data includes headers. In order to increase the flexibility (columns can be inserted), on the one hand, we specify that the header fields cannot be modified, and on the other hand, we represent the key columns in the form of variables
with ws lastrow = .usedrange.rows.count lastcol = .usedrange.columns.count arr = .range(.cells(3, 1), cells(lastrow, lastcol)).value leftdebitpos = pxy(arr, "Corporate debits", 2) leftcreditpos = pxy(arr, "Corporate lenders", 2) rightdebitpos = pxy(arr, "Bank debits", 2) rightcreditpos = pxy(arr, "Bank Lenders", 2) leftcheckpos = pxy(arr, "leftcheck", 2) rightcheckpos = pxy(arr, "rightcheck", 2)end with
(2) Circular array, compare the amount of the corresponding lender on the left and right, if it is the same, check the column on the right side of each and fill in the content, what content is not important, at first I used " " ", and then because of debugging**, it was changed to display the line number of the other party. If you find the same amount, you will move on to the next cycle.
if leftdebit = rightcredit then 'arr(i, leftcheckpos) = "√" 'arr(j, rightcheckpos) = "√" arr(i, leftcheckpos) = "r-" & j + 2 arr(j, rightcheckpos) = "l-" & i + 2 goto nextforend if(3) Write the result into the array, and mark the checked record with a color:
ws.range("a3").resize(ubound(arr), ubound(arr, 2)) = arrcall colorchecked(ws, leftcheckpos, rightcheckpos)The color of the ** was originally written in this process, but for some reason, there was always a problem, and then I tried to list it separately in a process, which was basically the same**, but it was normal, and it was a little confusing.
4. The process of generating the details of the outstanding accounts.
1) Load the checked "checklist" into the array arr.
2) Loop the array, extract the empty records of leftcheck and rightcheck, and store them in an array arrtem.
3) Write the results into the "Outstanding Accounts" table. Fault tolerance is done here, if there is no "unreached" table, we create a new one, and if there is, we clear the content.
5. Today's case should be said to have a certain practicability, especially for units with a lot of bank flows, and there is no financial software to support automatic reconciliation, which can reduce the workload of manual checking.
6. Of course, this case is in terms of function, there is still a lot of room for deep digging, and I am ready to continue to do it, and I will send it out first today, on the one hand, this function can be used, and there is no need to pursue perfection, on the other hand, it is to update the *** article:
1) One-to-many matching.
This mainly refers to the situation that there are consolidated bank documents in the accounts of the enterprise, and in turn, the amount of a bank flow corresponds to the amount of multiple corporate bank details, which cannot be said to be no, but can only be said to be very rare (I used to work in a group company, there are funds according to the department, and there is often a situation where a payment belongs to several departments, and when doing the accounts, it has to be divided into several amounts according to the department, resulting in very troublesome bank reconciliation. ）
When our financial personnel handle the accounts, they should decide whether they need to consolidate the bank documents to fill in the accounting vouchers according to the actual situation. If it often causes difficulties in bank reconciliation, then we should try to enter the amount of bank accounts one by one according to the bank documents. If in most months, the bank documents can be fully recorded, and the balance of the book deposit and the balance of the statement can often be consistent, then, for the same type of business, it should also be possible to make a consolidated document, such as bank charges, a few cents, there is no need to do it in one lump sum.
2) Directly generate the "Bank Deposit Balance Reconciliation Statement".
a) Read the outstanding accounts in the "Bank Deposit Balance Reconciliation Statement" of the previous month to the "Checklist" and participate in the checkbox.
b) Read the "Corporate Bank Deposit Ledger" and organize it into an Excel document in a fixed format according to the relevant fields of the checklist. Copying and pasting it in by hand is also fine.
c) Read the "bank statement", which is the electronic ** derived from the online banking, and the processing method is the same as above.
d) After the reconciliation is completed, press the name of the "bank account" in the second line of the "reconciliation form", plus "month" and save it as a separate file.
That's it for today!For full **, see second tweet.
~~~end~~~
If you like it, like it, click on it, leave a comment, and share it!Thanks for the support!
This article was first published on October 12, 2023 in my name***Excel live learning and application (VBA programming practice), more articles and cases, please search and pay attention!Case file sharing instructions: Please pay attention to my namesake vx***vba programming practice) to understand how to get the file, free!