reconciliation problem

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.
Maria keenmonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnBusiness Consultant (Owner)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.
0
Maria keenmonAuthor 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?
0
PatHartmanCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

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

Gustav BrockCIOCommented:
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.
0
Rob HensonFinance AnalystCommented:
Can you provide a sample file?

As suggested in the question, you may be able to use a pivot table to do this.
0
Dale FyeCommented:
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?
0
JohnBusiness Consultant (Owner)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.
0
David BernsteinChief DeveloperCommented:
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.
0
Maria keenmonAuthor 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!
0
Maria keenmonAuthor Commented:
Thank you, Experts!
0
Dale FyeCommented:
Welcome to EE, Maria.  We have a great crew of Access experts here to help.

Dale
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.