v lookup

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.
0
payroll (7)
8/8/2007 12:12:06 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
396 Views

Similar Articles

[PageSpeed] 50

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.
0
8/8/2007 2:34:02 PM
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.
0
Utf
8/8/2007 3:26:12 PM
Reply:

Similar Artilces:

Chinese fonts on Office v. X
I am having problems reading my documents that I receive from Taiwan and China (both simplified and traditional characters). When I usually receive an email with Chinese. . . I copy/paste them to Sherlock's translator and viola!. . . I get English. . . But this is not the case with attachments from emails in the form of excel or word files. I usually first have an issue in trying to find out which font they used so that the Chinese characters come out. Once they come out, I can print it out. . .but I need them translated. . . Unfortunately, I cannot copy/paste the characters at all. . ....

Lead v/s contact v/s account v/s opportunity
Hi, I am trying to understand a few basic things about Microsoft CRM. I would appreciate it if someone can enlighten me on the following questions: 1. Is the following definition of "Lead" correct? A lead is a person who we come to know from somewhere. Technically, a lead must have a name and optionally the company the lead belongs to. According to the CRM UI, a lead may be converted to one or more of the following: a) an opportunity b) a contact c) an account. Or, a lead may just disqualify. 2. What is a contact? When would I convert a lead into a contact? 3. What is an accoun...

Multiple V-Lookup
I am trying to do a Vlookup function that will look at 2 cells on the same row in one tab and find that in the workbook on the other tab in the same row and then spit back a number from a different cell. It's like a regular vlookup but i'm trying to search for 2 values in 2 different cells at the same time. Is this possible? Maybe it's not even a vlookup i should be using. The problem with using an if(and()) statement is i can only search for the combination 1 row at a time, so if it doesn't line up, i won't get a match. I need to search the whole workbo...

Lookup and count in the same formula
Hello, I need a formula that looks up a value in row 1 on sheet2 and returns the count of a range in the colmun that matches the lookup in sheet1. For example: Sheet2 - Feed Sheet Smithfield RSM John Doe ASM Jenn Smith ASM2 Brian Smith So Smithfield has 1 RSM and 2 ASMs Sheet1 RSM ASM Smithfield 1 2 So I want a formula to return the count of how many RSM's and ASM's are in a specific location. I have this: =(HLOOKUP(A1,Sheet2!A1:A6,(COUNTA(Sheet2!A5:A6)),FALSE)) but it's just retur...

The Lookup Wizard, Do I Hate It?
ok. So, this week the Lookup Wizard inflicted me with my first experience of it. I'm talking about the Lookup Wizard that appears in the Table Design interface. Here's the good news, you can bring up a table, and your field has a drop down and second level reference automatically working. The bad news is it presents the second level lookup everywhere, for everything. But sometimes I just want to see the index actually recorded in the table. If I build a query, and I just want the actual index rather than its translation, how can I get that? Is there a way to undo the autom...

SBS2003 and Hyper-v
We want to reduce equipment in the lab. We have a well functioning SBS2008 Standard domain running with one sbs2008 (8 GB) server and a second win2008 (8 GB system mem) server running our AVG service and Hyper-v. Under Hyper-v we have two guests, an XP Pro running old version of Quickbooks for company accounting and a Win2003R2 server running BlackBerry Enterprise Server running 4 blackberry phones. We have 10 real Vista Business workstations which will go Windows 7 Pro over the next two weeks. This all runs on 192.168.8.x and uses one public static IP address. We also have a ...

Uninstall old v. before downloading new?
I have tried the free trial Money Plus Deluxe on one computer, and now want to purchase and download to another computer which has Money 06 Premium loaded. Should I uninstall Money 06 Premium before downloading Plus Deluxe (after making a backup of the data, of course)? Is it OK to have both versions installed at the same time, at least until I have time to make sure everything has transfered correctly? Thanks for your help. In microsoft.public.money, Elizabeth wrote: >I have tried the free trial Money Plus Deluxe on one computer, and now want >to purchase and download to a...

I-v
does anyone know what this means, is it a word term ? No, and without a context with which to evaluate your post, it is meaningless. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, lizzy asked: | does anyone know what this means, is it a word term ? ...

V CARD
How can I import an outlook v-card/vcard into CRM easily. WHen i drag and drop the vcard into my "Contacts in CRM" folder, it does not show up. ...

Volatile UDF v ws_change conflict
Hi all In a worksheet there are cells that call an Application.Volatile UDF. The UDF does nothing other than return the rowheight of the cell that calls it. That worksheet also has a Worksheet_Change() event handler. If I delete the cells that call the UDF then the Worksheet_Change() routine works as planned. If I un-volatile the UDF then the Worksheet_Change() routine works as planned. But if I leave the UDF in the worksheet and leave it volatile (required) then the Worksheet_Change() event misbehaves. I shall post the full code of the UDF and the Worksheet_Change() if it would help, but I...

Advanced Lookups setup should let me exclude inactive records
Advanced Lookups setup should let me exclude inactive accounts, customers, vendors and employees as well as discontinued items. There should be an option in the lookup windows to include these inactive/ discontinued master records if desired. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

CListCtrl, Right clik-->Copy to clipboard-->Ctrl-V
Does anyone how to implement "Right Click-->Copy to Clipboard-->Ctrl-V" with CListCtrl control. I would like the functionality of copying the content of a CListCtrl into Word editor. Thanks, cy163 -- cy163cy163 ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------ for(int i = 0; i < ctl.GetCount(); i++) // or GetItemCount, I forget { ...form string based on elements in CListCtrl } Write string as CF_TEXT. Note that i...

Numbers V Text
I want to print out a document but I don't want it all printed out in numbers I want the text printed out. Here is what I don't want it say 223, I want to say =SUM(C7:C11)/15 I am sure it can be done via "Options" but I can't work out how. Jim Ducat Toggle the view to Formula View - Ctrl + ` (that's the same key as the tilde ~) MRO "James Ducat" <jd007f1618@blueyonder.co.uk> wrote in message news:x627b.16604$qK6.394@news-binary.blueyonder.co.uk... > I want to print out a document but I don't want it all printed out in > numbers I wan...

Lookup? #2
Workbook 1 Associate ABRAMS,TANJA BAILEY, NAKEYTA BAIROS, VERA BAKER,JANINE BANDISON,LINA Workbook 2 Associate Orders Avg $$ Total % ABRAMS,TANJA 52 52.40 5.77 BAILEY, NAKEYTA 83 62.97 26.51 BAIROS, VERA 105 55.19 9.52 BAKER,JANINE 88 61.41 14.77 BANDISON,LINA 138 74.93 6.52 BARRY,DIANE 75 66.19 6.67 BEAUBRUN,CHERLINE 84 57.30 7.14 BELL,CHERYL 44 64.95 9.09 I want to open a master sheet (Workbook 1) and search for someones name in Workbook 2. If there name ...

Search Column & Row using CMap, Lookup
hi all , iam reading a .dat file with the following data pattern . 3001, 200 ,4005 ,5006 000 , 47.12 ,785 ,699 the first column is the key value , for ex - 3001 is the key . if i search for the key value( 3001) , and if it is found , i need to extract the data value at 3rd Column of the row ( 4005). can someone give an example ( using CMap, Lookup ) . This is pretty simple. First, create a structure class Data { public: UINT key; TYPE_OF_YOUR_CHOICE col1; TYPE_OF_YOUR_CHOICE col2; TYPE_OF_YOUR_CHOICE col2; }; Read the line, parse out the values, and store them...

Installing new copy of Office Mac v.x: Delete old one first?
I had the same serial # of Office v.x installed on 2 Macs on my home network, so we could use it at either location (but only a single location at a time). Now with my wife teaching fulltime we both need to use Word and PowerPoint a lot, often concurrently. We purchased a second copy of Office, the Academic version this time, and I am trying to find out -- before starting -- whether I need to completely uninstall the earlier Office suite ( and the troublesome serial number) before I try to install the new Academic version. It doesn't appear that Office ships with a de facto uninstaller pr...

DPM 2010 with Hyper-V Live Migration Cluster protection and MSA200
Hello Everybody, we started trying the DPM2010 to protect our Hyper-V R2 "live migration" Environement. Our architecture is a simple 2 node cluster with an attached HP MSA2000 unit. we encountered some difficulties in the backup process, I'll try to explain: When starting the Virtual Machine backup using the child partition snapshot we got some errors in the Windows Event Log like The first is an error generated by CAPIVSSProvider (I think the hardware VSS provider of HP): capiCreateSnapshots() : Erreur Capi 0x000000BA (Error: CAPI_ERROR_EXCEED_MAX_SNAPS...

V
Hi all, How do you assign a symbol/name to a cell? E.g. instead of =if(G10>1,"Yes", "no") , change it as =if(L>1,"Yes", "no"). -- Air Lancer Select G10. Enter "L" (without quotes) in the Name box at the left of the Formula Bar. In article <40250085-8000-448B-A1D2-FD2050AB444C@microsoft.com>, "Air Lancer" <chesserhawk-email.yahoo.com.sg> wrote: > How do you assign a symbol/name to a cell? > > E.g. > > instead of =if(G10>1,"Yes", "no") , change it as =if(L>1,...

Filtering a lookup field
Hi everyone, Has anyone looked at filtering lookup fields? Essentially want i want to do is filter the primary contact on the business form [crm 3.0] so that it only shows the clients of that business, rather than everyone. Is that possible? Also, is it possible to add other lookups so that there could be secondary contact, billing contact, manager contact... etc? Thanks, Jack ------=_NextPart_0001_10CB3D68 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Jack~ You won't be able to do this with CRM out of the box. However, you may be able to do this with SDK. I would t...

lookup i think!
Hi I am trying to set up a simple function. i want a list of paints see example below. I have managed to create a list using a validation function. What i can't seem to do is to display a price to the right of the item. So when i select an item from the list it picks a price which i store in the same workbook as the products list and it should appear in the cell to the right. For example if someone picks red paint then the price for that appears in the same row, one or two rows to the right. I want to link the product with a price. red paint 10.00 blue paint ...

How to retrieve values from a lookup table
I want to default the OwnerID of a Case based on the OwnerID of the selected Customer. I know how to retrieve the typename, name and GUID from a LookUp object (in this case the CustomerID) but how can I retrieve associated values from the related table? Hi, You need to write a webservice to fetch the owner of the customer and than use that webservice withs JScript in onLoad of the case to set the owner of the case. -- PLEASE do click on Yes or No button if this post was helpful or not for our feedback. uMar Khan Email for direct contact: imumar at gmail dot com "Agile CRM...

DPM 2010, Server 2008, Hyper-V, Error 31313
I run a Windows Server 2008 with runs Hyper-V Role and DPM 2010 RC Server. If I add a local Hyper-V to a protection group, I get an error to install kb948465 (SP2) and kb971394. SP2 was already installed, so I installed kb971394. After server reebot I am still not able to add local hyper-v guest to protection group. I have read some posts about this error, but mostly them occured in cluster environments I dont have. Any hints? Thank you... Hi, Please install the RTM bits of DPM 2010 which contain a fix for the scenario when protecting local hyper-v guests. Thanks, -...

V/HLookup
I have a table of product codes with 20 different prices for each product code that sit on sheet 2 of the workbook. Price Range prod code 1 2 3 4 a 10 20 30 40 b 15 25 35 45 c 17 19 21 22 On Sheet 1, in cells A6 to A50 I want to enter various product codes, and depending on what price range I have entered in cell A1, I would like to be able to return the price next to the product code in column B ie: Price range A1 =4 Product code=A6 =b Returns price of 45 in ...

Reinstalling Microsoft Office v.X
- Downloaded trial version of Microsoft Office, - Forty day trial period came and went - Did not want, - Cleanly removed same, - Attempted to access both Word and Excel (Microsoft Office v. X) - Question mark superimposed over each icon and could not access either program, - Reinstalled program,(treated as new owner) - New product I.D. assigned, - Attempted to register on-line, - Message, "Unable to service your request". - Contacted Microsoft - No support as of January 01 / 07. What is the process to reinstall my original Microsoft Office v. X? Hello George - There's no need...

upgrading from Office v. 4.2.1
Hi, I have version 4.2.1 on Microsoft Office. Can I use the upgrade version of Office 2004 for Mac to upgrade this version? Thanks. Any help would be appreciated. In article <1112793138.437316.197000@z14g2000cwz.googlegroups.com>, john.randino@verizon.net wrote: > I have version 4.2.1 on Microsoft Office. Can I use the upgrade version > of Office 2004 for Mac to upgrade this version? > Thanks. Any help would be appreciated. Unfortunately, the earliest version eligible for upgrade is Office 98. However, you may be able to find a copy of Office 98 or 2001 pretty cheap on Eb...