Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reconciling
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reconciling
On Sep 22, 9:33 am, Hscott wrote:
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reconciling
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" wrote: On Sep 22, 9:33 am, Hscott wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reconciling
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing and Reconciling 2 excel tables | Excel Discussion (Misc queries) | |||
Reconciling two lists | Excel Worksheet Functions | |||
creating a reconciling list of items not matched between two files | Excel Worksheet Functions | |||
Reconciling Data Ranges | Excel Discussion (Misc queries) |