reconciliation problem

Maria keenmon
Maria keenmon used Ask the Experts™
on
Hello,
I have a list of debits and credits and each has a unique voucher number.  There are duplicate amounts in the list but the voucher numbers are unique.  For instance, there could two identical debits and corresponding credits but each has a unique voucher number.  Some of the credits are not debited and we need to identify them.  I'd appreciate any help you can provide to help me develop a formula or pivot table in Excel or a query or form in Access.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
We do these weekly at least and normally more frequently.

List in ordinary Excel by Voucher Number (unique). That should pair debits and credits for the voucher or zero if it does not exist.  

Sort paired , reconciled vouchers to the top (or bottom) leaving a small number of non-cleared transactions.

That, in a nutshell, is how we do this.

Author

Commented:
Hi, John,
Thanks for your comment; it;s very helpful.  I was hoping that the process could be automated with some function or macro that would seek to match every debit with a credit and produce a list of unmatched.  I wonder if an unmatched query in Access might do it.  Have you found a way to automate the process rather than manually reviewing the entries?
Distinguished Expert 2017
Commented:
To do this as you describe in Access, you would need some way to identify which debit matched which credit and you don't seem to have that.  Simply joining debits to credits on the amount field would simply result in an idiotic result which would not help at all.  If you had 20 debits of 34.12 and 21 credits of 32.12, your resultset would include 20 * 21 or 420 rows.  Meaningless.  

May be you should go at this differently.  Create a query that lists debits by amount with a count and another that lists credits by amount with a count.  Then you could join the two queries on amount and if the counts are different, you could display those as unmatched.  If it is possible to have debits that don't match any credits or credits that don't match any debits, you would need to use a full outer join rather than a single query.  To do that you would create the join for the two count queries inside a union query so you could do a left join of debit to credit and union that with a right join of debit to credit to pick out the outliers.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Order the records/list after voucher number.

Then loop through the debits.
For each, look up the oldest (lowest voucher number) having a credit of that amount, and - if found - mark the two records as reconciled.

This will leave the newest (highest voucher number) credits marked as not reconciled.

When new credits and/or debits are recorded, repeat the process.
Rob HensonFinance Analyst

Commented:
Can you provide a sample file?

As suggested in the question, you may be able to use a pivot table to do this.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Do the records have a date/time stamp, or is the voucher number always incrementing?

Which should come first in your system, the debit, or the credit?
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
We typically do not have many unmatched entries, so sorting by unmatched gets us to a small review place. There hasn't been any payback in automating it at this point.
David BernsteinChief Developer/ Microsoft Access

Commented:
I think that I understand the problem and have solved similar ones in the past. If you supply a data file I can most likely provide a solution for you.

Author

Commented:
Sorry for the delay in responding.  Thank for all the great comments and offers to help.  Pat's suggestio makes the process much faster and requires a small investment of time to prepare the file.  This is the fiirst question I've posted and I'm pretty amazed at the great response from you all.  Thank you!

Author

Commented:
Thank you, Experts!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Welcome to EE, Maria.  We have a great crew of Access experts here to help.

Dale
Distinguished Expert 2017

Commented:
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial