PDA

View Full Version : Reconciliation based on recurring values



kalakriti
11-22-2019, 12:43 PM
Hi
,

I have a two sets of statement, one from the client [ referred as Client books] needs to be reconciled with the co. ledger [ Co. books] .
there are lot of transactions , every month and most of the times the amounts are similar for different transactions .

Now the challenging part being absence of any common reference in both the statements which would have simplified my life .

without the absence of a common reference , I go on manually knocking of based on the amount from both the statements .


Amount appears as positive in the clients books while it appears as negative amount in Co books


I am attaching the excel sheet for your reference



What I am looking for is i
n column F , I need a formula which will check the value of column D , appearing against the client books and match with the similar amount appearing with the co books , if Matched it will say matched in column F

and then give the corresponding reference appearing in column B for the respective amounts in Column G.


Hope I have explained it clearly, any help would be really appreciated .

werafa
11-23-2019, 09:41 PM
can think of several ways to start - but the simple versions will require helper columns.

create a column index row in your client lookup sheet - make it the spreadsheet row number for ease of reference
once this is done, you can use Vlookup.

vlookup(-1*your value, client table range, offset to client helper column with row ID, false)
(the 'false' gives exact match or nothing)

the problem with this (and similar) formula approaches, is that one value may be matched multiple times
your other option is to use VBA to do something similar, but to mark the client spreadsheet once a value has been matched. you can then exclude it from subsequent matching

SamT
11-24-2019, 10:16 AM
You can NOT reconcile AcctsRcv like that. You can NOT expect clients to pay what is owed, some may underpay sometimes, some may over pay sometimes, some may make two, or more, payments at the same time. Some might even skip town.

The only times you gather both Company invoices to, and receipts from, a client is when they have to go to Legal/Collections.


I have a two sets of statement, one from the client [ referred as Client books]
That doesn't really tell us much about your exact situation.

This is an example of one way to maintain client balances. On a separate sheet:


Clients List


Client 1






Client 2








Client 1 (http://www.vbaexpress.com/forum/#RANGE!C1)

Date
Co Inv #
Inv Amt
Cl Inv #
Rcved
Balance

Date
Co Inv #
Inv Amt
Cl Inv #
Rcved
Balance



Client 2 (http://www.vbaexpress.com/forum/#RANGE!J1)

















Client 3 (http://www.vbaexpress.com/forum/#RANGE!U1)

















Client 4





















Note that you would only ever fill in Date and Co Inv# and Inv Amt or Date And Cl Inv# and Rcved.If you had an invoice and a payment on the same date, that should take two entries. Inv Amt should be negative and Rcved should be positive.
Cl Inv# can be any document number, check number or even "Cash". It is just a Notes cell.
Balance is a simple formula that can be dragged down
Column A is merely links to each client table, it can be easily kept current with VBA.
Note the empty columns between client tables. This is important.

werafa
11-24-2019, 02:48 PM
Hi SamT,

agree to your key points - but my understanding of his question is to find and cancel out the payments that do match - leaving only those that do not for further reconciliation.
Still, as you say, a total owed and a total paid to date may well circumvent his question.