If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A challenge for you? Querying one sheet against another
First thing I should point out is:
I don't have access to Access therefore Excel is my only route towards what I'm looking for, which is... I need to compare my mobile phone bill (downloaded into a worksheet from my online account pages) with the contents of my 'Contacts' exported from Outlook. The contacts will be exported so that only my 'work' phone numbers are in the worksheet, I then need to be able to compare this with my bill's sheet and pull out (query report) any phone calls with numbers that match - the total time of these 'matched' calls will be the amount I claim on expenses... Can I do this with Excel (2000/2003 is what I've got) and if so, how? Many TIA Ian. |
#2
|
|||
|
|||
A challenge for you? Querying one sheet against another
Hi Ian,
Suppose your called numbers are in column A, the call costs are in column B, and the 'work' phone numbers are in column C on rows 1-10. In cell D1, you could enter: =SUMIF(A:A,C1,B:B) and copy this down to row 10. This will give you the sum of the call costs for each of the numbers in column C that corrrespond with the numbers in column A. Cheers "Ian Usher" wrote in message ... First thing I should point out is: I don't have access to Access therefore Excel is my only route towards what I'm looking for, which is... I need to compare my mobile phone bill (downloaded into a worksheet from my online account pages) with the contents of my 'Contacts' exported from Outlook. The contacts will be exported so that only my 'work' phone numbers are in the worksheet, I then need to be able to compare this with my bill's sheet and pull out (query report) any phone calls with numbers that match - the total time of these 'matched' calls will be the amount I claim on expenses... Can I do this with Excel (2000/2003 is what I've got) and if so, how? Many TIA Ian. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.627 / Virus Database: 402 - Release Date: 16/03/2004 |
#3
|
|||
|
|||
A challenge for you? Querying one sheet against another
Ian
How about another approach? You can automate Outlook instead of exporting your contacts. Assume you have three columns of data in your exported phone bill: A = Time, B = Phone number, C = Duration of Call. Now you can set a reference (Tools - References) to the Microsoft Outlook Object Library and use code like this Sub MatchPhoneBill() Dim olApp As Outlook.Application Dim olCont As Object Dim olNs As Outlook.NameSpace Dim olFldr As Outlook.MAPIFolder Dim rCell As Range Dim rRng As Range Dim sPhone As String 'All the entries in column A With ActiveWorkbook.Sheets(1) Set rRng = .Range("a2", .Range("A" & .Rows.Count).End(xlUp)) End With 'Create Outlook link Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set olFldr = olNs.GetDefaultFolder(olFolderContacts) 'Loop through all the phone records For Each rCell In rRng.Cells 'Format the phone number to look like Outlooks sPhone = "(" & Left(rCell.Offset(0, 1).Value, 3) & ") " _ & Mid(rCell.Offset(0, 1).Value, 4, 3) & "-" _ & Right(rCell.Offset(0, 1).Value, 4) 'Find the first contact with that phone number Set olCont = olFldr.Items.Find("[BusinessTelephoneNumber] = " _ & Chr(34) & sPhone & Chr(34)) 'If one is found, write the name to column D If Not olCont Is Nothing Then rCell.Offset(0, 3).Value = olCont.FullName End If Next rCell Set olFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub This will put the names of your contacts next to their record. Finally, run a pivot table on the phone records with FullName as the row and Sum of Duration as the Data. Anything under (Blank) in your pivot table means they aren't in your Contacts. You'll have to adjust a few things like formatting the phone number (it may not be necessary) and identifying the next blank column to write the name. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Ian Usher" wrote in message ... First thing I should point out is: I don't have access to Access therefore Excel is my only route towards what I'm looking for, which is... I need to compare my mobile phone bill (downloaded into a worksheet from my online account pages) with the contents of my 'Contacts' exported from Outlook. The contacts will be exported so that only my 'work' phone numbers are in the worksheet, I then need to be able to compare this with my bill's sheet and pull out (query report) any phone calls with numbers that match - the total time of these 'matched' calls will be the amount I claim on expenses... Can I do this with Excel (2000/2003 is what I've got) and if so, how? Many TIA Ian. |
Thread Tools | |
Display Modes | |
|
|