v lookup

payroll · External Since: Aug 07, 2007 Posts: 1

I am inexperienced with excel so any suggestions are welcomed. I have two
different spreadsheets with columns of name, hours worked, total wages. Each
name has a different row for each type of wage. A person can have several
rows. There are no subtotals. I want to know how to compare each spreadsheet
and find the differences. Essentially I want to know if the total wages on
the first spreadsheet match the second spreadsheet. If wages have been
partially or completely dropped, or duplicated etc... a report would
generate. Similar to balancing a checkbook. Thanks.

Sean Timmons · External Since: Dec 17, 2006 Posts: 298

This can be done different ways depending on a couple things:

If you know all the names that will be on the 2 sheets, and you just want to
see when there's a mismatch, you can do:

Column A Column B
Rep Name Disposition
John
=sumif(Sheet1!A:A,A2,Sheet1!C:C)-sumif(Sheet2!A:A,A2,Sheet2!C:C)

This will show the $ difference between sheet1 and sheet2 for each rep name.
If it's positive, this means sheet 1's wages are higher, and vice versa.

That's a simple method, but does depend on you knowing all the names from
both spreadsheets in advance.

HTH

"payroll" wrote:

> I am inexperienced with excel so any suggestions are welcomed. I have two
> different spreadsheets with columns of name, hours worked, total wages. Each
> name has a different row for each type of wage. A person can have several
> rows. There are no subtotals. I want to know how to compare each spreadsheet
> and find the differences. Essentially I want to know if the total wages on
> the first spreadsheet match the second spreadsheet. If wages have been
> partially or completely dropped, or duplicated etc... a report would
> generate. Similar to balancing a checkbook. Thanks.

JLatham · External Since: Sep 19, 2006 Posts: 1511

What Sean Timmons has offered may be all that you need. If you need more,
you're probably not going to be able to use simple SumIf() and if you need to
compare 2 or 3 fields, as name and pay type along with amount paid, then
VLookup() will not work either as it will stop looking at the first match.

Instead your best bet is a SUMPRODUCT() formula, or a few of them to do the
work. Rather than try to explain it all, I've uploaded a sample workbook
with setups of the formulas needed for those samples. On 2 pages simulating
your 2 pages, I've set formulas to compare row by row with the other sheet,
while on a 3rd sheet I've set up the sheet1 vs sheet2 and sheet2 vs sheet1
comparisons in 2 columns.

To do an audit, or reconciliation, have to look at what's going on in both
sheets. If you don't have something on one sheet that exists on the other,
you cannot do a comparison of the two - you can't even tell that it is
missing easily. So you have to look at all items on both sheets asking "is
this on the other sheet also?".

To examine the sample workbook, just click on this link and save to your
hard drive then open it up.
http://www.jlathamsite.com/uploads/for_payroll.xls

"payroll" wrote:

> I am inexperienced with excel so any suggestions are welcomed. I have two
> different spreadsheets with columns of name, hours worked, total wages. Each
> name has a different row for each type of wage. A person can have several
> rows. There are no subtotals. I want to know how to compare each spreadsheet
> and find the differences. Essentially I want to know if the total wages on
> the first spreadsheet match the second spreadsheet. If wages have been
> partially or completely dropped, or duplicated etc... a report would
> generate. Similar to balancing a checkbook. Thanks.