Reconciling

[Hscott]

I have a very large file that we have to reconcile monthly. There are two
worksheets, one is an A/P aged invoice and one has a list of all of our
payments to vendors. I need to be able to match the vendor names from one
column on each sheet and then match them to their corresponding invoice
subtotals. If there is no match then we know to reconcile that account.
Currently this process takes about 4 hours...on a good day. Anything that
will help I would appreciate. Let me know if you need a sample of what I'm
referring to.

 

[Spiky]

I have a very large file that we have to reconcile monthly. There are two
worksheets, one is an A/P aged invoice and one has a list of all of our
payments to vendors. I need to be able to match the vendor names from one
column on each sheet and then match them to their corresponding invoice
subtotals. If there is no match then we know to reconcile that account.
Currently this process takes about 4 hours...on a good day. Anything that
will help I would appreciate. Let me know if you need a sample of what I'm
referring to.

Should be fairly simple with VLOOKUP or another lookup function. You
may have to give an idea of your data's layout for good help, though.
So, yes to the sample.

For visual usage/workflow reasons, I'd probably use 2 columns (or
maybe even a separate table format on a 3rd sheet), although you could
do it all in one column if you prefer. The first column would do a
lookup to put the invoice and payment numbers together. The second
would be a quick logical test to highlight whether they match. And you
can either highlight the False answers or maybe Sort to put them all
together to get an easy list to reconcile. Depends on your format,
really.

 

[Hscott]

Here is a brief example of what my date looks like. I basically have the
same information on both worksheets, just different columns.

Worksheet 1
Vendor Name Amount Due
ACDI $221.00
ACDI $131.00
Subtotal $352.00
RENISHAW $218.00
RENISHAW $162.00
Subtotal $380.00

My 2nd Worksheet is just a bit different
Vendor Name Amount Due
ACDI $221.00
$131.00
Subtotal $352.00
RENISHAW $218.00
$162.00
Subtotal $380.00

Ideally, I want to be able to type in the vendor name in a cell on the
reconciliation page and have it tell me if their subtotals match and notify
me if they don't. I have an if function in there, but I don't know how to
make sure I'm getting the right vendor matched with the right dollar amount.

 

[Spiky]

There are various ways to look up info. Here's one.

I would probably have the reconciliation sheet show both numbers so I
can see the difference when necessary. So say Col A is the vendor name
and B is the reference to the first sheet, C to 2nd sheet, D = B minus
C.

This can go in B2 and copy down (you'll have to modify to your sheet
names and ranges):
=SUMIF(Sheet1!$A$2:$A$1000,A2,Sheet1!$B$2:$B$1000)

It would be easier to do the second sheet if it had the vendor names
on every line like the first one. Or if the Subtotal description
included the vendor name, you could have Excel search on that. I think
there is a way to find the vendor, then skip down to the first row
after that with "Subtotal" in it, and return that amount, but I don't
know how offhand. Much easier if you could change it just a bit.

Also, generally speaking, you need to make sure there aren't errors or
typos in the vendor names anywhere. Otherwise a lookup can miss
something. Sometimes you can get around that with wildcard characters,
but if you have vendors with very similar names (often cities or
states in the name), wildcards can be more of a problem than a help.